MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Want to Get All Matches for List and Cocatenate

Posted by Mike on January 15, 2002 5:01 PM

If I have an linked text file that gives me

Circuit Room
pp1-1 355
pp1-1 356
pp1-1 357
pp1-2 358
pp1-2 359

How do I get a formula to get the next MATCH for "*-1" and then the next MATCH and cocatenate them so I can have the output look like this:

ROOM 355, 356, 357
for circuit number 1?

Thank you for your help.

Posted by Aron on January 16, 2002 12:42 AM

Set up your worksheet like this: In A1:D6 (for example):

Circuit Room Circuit Rooms
pp1-1 355 1
pp1-1 356 2
pp1-1 357
pp1-2 358
pp1-2 359

And use the following macro:
Sub Join()
On Error GoTo xit
Set cir1 = Range("A2:A6")
Set rms2 = Range("D2:D6")
Let r = "Room "
For Each c In rms2.Cells
For Each d In cir1.Cells
If c.Offset(0, -1).Value = "" Then GoTo xit
If d.Value Like "*-*" & c.Offset(0, -1) Then
Let x = d.Offset(0, 1).Value
Let r = r & x & ", "
End If
c.Value = r
r = "Room "
Exit Sub
End Sub


Range("A2:A6") is where your "pp1-1" is stored

Range("D2:D6") is where you want "Room 355, 356.." to be entered.