Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
Hi,
I have a worksheet with 31,000 rows of data. I would like to use a vlookup to return all the values listed in column b with its corresponding value is column A Colum A Column B 000000xxc093NEF 0 20050225 Modality 000000xxc093NEF 0 20050225 CMT 000001239619UAL 0 20050126 Visit 000001239619UAL 0 20050126 E & M I would like it to return: Column A Column B 000000xxc093NEF 0 20050225 Modality, CMT 000001239619UAL 0 20050126 Visitss, E & M I have search many threads, but just can't seem to find the one that fits my need. Thank you so much!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
That's not really what =vlookup() does.
But you could use a macro to do it. Make sure row 1 has headers in it and I'm assuming that there isn't anything else in the worksheet besides the stuff in columns A and B. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 2).Value _ = CurWks.Range("a1").Resize(1, 2).Value With CurWks .Range("a:b").Sort key1:=.Range("a1"), order1:=xlAscending, _ key2:=.Range("b1"), order2:=xlAscending, _ header:=xlYes FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then 'both col A and B are the same, do nothing! 'those values are already taken care of Else 'Col A is the same, but col B is different 'so just add a comma and that value to the output NewWks.Cells(oRow, "B").Value _ = NewWks.Cells(oRow, "B").Value & ", " _ & .Cells(iRow, "B").Value End If Else 'Col A is is different--drop down a row in the output 'and plop in the first values oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value End If Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub This sorts the original data by column A, then by column B and just goes through each row looking for differences. If Col A and col B are the same, it's a duplicate and nothing happens. If Col A is the same, but col B is different, it appends that value to the current cell in the output worksheet. If Col A is different (then we don't care about column B), we just drop down a row in the output and put the new values in that new row. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm "Susana C via OfficeKB.com" wrote: Hi, I have a worksheet with 31,000 rows of data. I would like to use a vlookup to return all the values listed in column b with its corresponding value is column A Colum A Column B 000000xxc093NEF 0 20050225 Modality 000000xxc093NEF 0 20050225 CMT 000001239619UAL 0 20050126 Visit 000001239619UAL 0 20050126 E & M I would like it to return: Column A Column B 000000xxc093NEF 0 20050225 Modality, CMT 000001239619UAL 0 20050126 Visitss, E & M I have search many threads, but just can't seem to find the one that fits my need. Thank you so much!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
I LOVE YOU GUYS!
Thank you so much for your help, this worked wonderfully! Dave Peterson wrote: That's not really what =vlookup() does. But you could use a macro to do it. Make sure row 1 has headers in it and I'm assuming that there isn't anything else in the worksheet besides the stuff in columns A and B. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 2).Value _ = CurWks.Range("a1").Resize(1, 2).Value With CurWks .Range("a:b").Sort key1:=.Range("a1"), order1:=xlAscending, _ key2:=.Range("b1"), order2:=xlAscending, _ header:=xlYes FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then 'both col A and B are the same, do nothing! 'those values are already taken care of Else 'Col A is the same, but col B is different 'so just add a comma and that value to the output NewWks.Cells(oRow, "B").Value _ = NewWks.Cells(oRow, "B").Value & ", " _ & .Cells(iRow, "B").Value End If Else 'Col A is is different--drop down a row in the output 'and plop in the first values oRow = oRow + 1 NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value End If Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub This sorts the original data by column A, then by column B and just goes through each row looking for differences. If Col A and col B are the same, it's a duplicate and nothing happens. If Col A is the same, but col B is different, it appends that value to the current cell in the output worksheet. If Col A is different (then we don't care about column B), we just drop down a row in the output and put the new values in that new row. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Hi, [quoted text clipped - 22 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |