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.
Mike
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
Next
c.Value = r
r = "Room "
Next
xit:
Exit Sub
End Sub
Where:
Range("A2:A6") is where your "pp1-1" is stored
And
Range("D2:D6") is where you want "Room 355, 356.." to be entered.
HTH,
Aron
