Vlookup

  • Thread starter Susana C via OfficeKB.com
  • Start date
S

Susana C via OfficeKB.com

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!!
 
D

Dave Peterson

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
 
S

Susana C via OfficeKB.com

I LOVE YOU GUYS!

Thank you so much for your help, this worked wonderfully!

Dave said:
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,
Click to expand...
[quoted text clipped - 22 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200607/1
Click to expand...
Click to expand...
 
Top