| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
| ranking query | Excel Discussion (Misc queries) | |||
| Extracting data from two columns | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
Hybrid Mode
