Extracting data
 

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.

Extracting data
 

Gingit Wrote:
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.
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
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=551706

Extracting data
 

Thanks jindon I'll try it.

Gingit

"jindon" wrote:


Gingit Wrote:
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.
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
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=551706