Extracting data

[Gingit]

A B C D
1 Toyota Chev Buick
2 xxxyz 500 100
3 trxxx 300
4 yyzsz 200

I have a sample of the following spreadsheet. I want to extract out only
the data that matches columns B-D and the number. In other words I want to
make a list that matches a car with the data and only lists if it has data
assoicated with it.
For ex. give me Toyota and xxxyz but I don't wont Toyota and trxxx since it
has no data associated with it.

Thanks in advance.

 

[jindon]

A B C D
1 Toyota Chev Buick
2 xxxyz 500 100
3 trxxx 300
4 yyzsz 200

I have a sample of the following spreadsheet. I want to extract ou
only
the data that matches columns B-D and the number. In other words
want to
make a list that matches a car with the data and only lists if it ha
data
assoicated with it.
For ex. give me Toyota and xxxyz but I don't wont Toyota and trxx
since it
has no data associated with it.

Thanks in advance.

The code below will change the data, so make back-up file before yo
run.

Code
-------------------

Sub test()
Dim a, result(), i As Integer, ii As Integer, n As Integer
With Range("a1").CurrentRegion
a = .Value
.Celar
End With
For ii = 2 To UBound(a,2)
For i = 2 To UBound(a, 1)
If Not IsEmpty(a(i, ii)) Then
n = n + 1
Redim Preserve result(1 To 2, 1 To n)
result(1, n) = a(1, ii)
result(2, n) = a(i, 1)
End If
Next
Next
Range("a1").Resize(n, 2) = result
Erase a, result
End Su

 

[Gingit]

Thanks jindon I'll try it.

Gingit

The code below will change the data, so make back-up file before you
run.

Code:
--------------------

Sub test()
Dim a, result(), i As Integer, ii As Integer, n As Integer
With Range("a1").CurrentRegion
a = .Value
.Celar
End With
For ii = 2 To UBound(a,2)
For i = 2 To UBound(a, 1)
If Not IsEmpty(a(i, ii)) Then
n = n + 1
Redim Preserve result(1 To 2, 1 To n)
result(1, n) = a(1, ii)
result(2, n) = a(i, 1)
End If
Next
Next
Range("a1").Resize(n, 2) = result
Erase a, result
End Sub