VLookUp function to return multiple rows

[Guest]

I have set of data

Key Text
1 x
1 y
1 z
2 a
2 b
3 c
3 d

I want to use a function that will return

Key Text
1 x,y,z
2 a,b
3 c,d

I apply the formula:

VlookUp() to the first data set and I only receive the following output

Key Text newText
1 x
2 a
3 c

Does anyone know of a different formula I can use inplace of VLookUp()??

I have tried to apply the following code from a previous example, but this
gives the same results:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=E3,ROW($A$1:$A$30)),ROW(A1)))

Can anybody help?

 

[Guest]

I don't think you can do it without code:

Try this .... input Sheet1, output Sheet2


Dim r As Long, rr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim constr As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

rr = 1
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
constr = ""
For r = 2 To lastrow
If .Cells(r, "a") = .Cells(r + 1, "A") Then
constr = constr & .Cells(r, "B") & ","
Else
constr = constr & .Cells(r, "B") & ","
rr = rr + 1
ws2.Cells(rr, 1) = .Cells(r, "A")
ws2.Cells(rr, 2) = Left(constr, Len(constr) - 1)
constr = ""
End If
Next r
End With

HTH

 

[Guest]

How do I run this function or apply the variables?

 

[Guest]

Never mind the last post, that worked brilliantly.

Thank you very much.

Did you write the code yourself or pull it from somewhere???

 

[Guest]

Take a look here to start:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brief synopsis here:

I have added the "Sub" statement at beginning and end. You can copy all the
code below into the module using the Visual Basic Editor.

In Excel Press Alt+F11 to start it up, right click on the VBAProject in the
project window, Insert==>Module. Copy the code below and paste into the
module.

Click anywhere in the code and then click the "Run Macro" (green arrow head)
button.

Give it a go!


Sub abc()

Dim r As Long, rr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim constr As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

rr = 1
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
constr = ""
For r = 2 To lastrow
If .Cells(r, "a") = .Cells(r + 1, "A") Then
constr = constr & .Cells(r, "B") & ","
Else
constr = constr & .Cells(r, "B") & ","
rr = rr + 1
ws2.Cells(rr, 1) = .Cells(r, "A")
ws2.Cells(rr, 2) = Left(constr, Len(constr) - 1)
constr = ""
End If
Next r
End With

End sub

 

[Guest]

Glad to hear it's worked OK.

Coded myself but requests to reformat data are common so it doesn't take
long to put together. It might be useful again.

(I learn a lot from these NGs .. it's never ending!)

 

[Herbert Seidenberg]

For a non-VBA solution see
Merge Rows....Apr 19 2006

 

[Harlan Grove]

Toppers wrote...

I don't think you can do it without code...

....

Well not if there could be more than a few dozen items to concatenate,
but if there were fewer items, no code required.

Would this always be sorted by Key? I'll assume so, and I'll also
assume the table above, excluding the top row of labels, is named Tbl.
....

If the result range were in D1:E4 with labels in D1:E1, try these
formulas.

D2:
=INDEX(Tbl,1,1)

E2:
=INDEX(Tbl,1,2)&IF(COUNTIF(INDEX(Tbl,0,1),D2)>1,","&INDEX(Tbl,2,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D2)>2,","&INDEX(Tbl,3,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D2)>3,","&INDEX(Tbl,4,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D2)>4,","&INDEX(Tbl,5,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D2)>5,","&INDEX(Tbl,6,2),"")

D3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+1,1)

E3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+1,2)
&IF(COUNTIF(INDEX(Tbl,0,1),D3)>1,
","&INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+2,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D3)>2,
","&INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+3,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D3)>3,
","&INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+4,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D3)>4,
","&INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+5,2),"")
&IF(COUNTIF(INDEX(Tbl,0,1),D3)>5,
","&INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),D$22))+6,2),"")

Fill D3:E3 down as far as needed.

On the other hand, if one uses Laurent Longre's MOREFUNC.XLL add-in,
the column E formulas could be reduced to

E2 [array formula]:
=MID(MCONCAT(IF(INDEX(Tbl,0,1)=D2,","&INDEX(Tbl,0,2),"")),2,255)

Fill E2 down as far as needed.