Lookup = values & concatenate to correct cell location

[Rob Ford]

I have a worksheet with a list of names and an integer of
anywhere between 1 to 50 next to them, I have a worksheet
that has a column with cells 1 to 50; I want to
concatenate names that have the same integer into the
appropriate cell.

eg.

Worksheet 1.
John 1
Mary 3
Dave 1
Matt 3
Dean 1

Worksheet 2.
1 John, Dave, Dean
2
3 Mary, Matt
4
5

I would really appreciate some help on this,

Regards,
Rob.

 

[Cecilkumara Fernando]

Rob,
Hope you already got a formula solution,
here is some code to do the job

Sub Groupit()
Dim LR As Integer
Dim LR1 As Integer

LR = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & LR).TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

Range("B1").Formula = "Index"
Range("B1:B" & LR).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("F1"), Unique:=True

LR1 = Range("F" & Rows.Count).End(xlUp).Row

Range("F1:F" & LR1).Sort Key1:=Range("F1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

For i = 1 To Range("F" & LR1).Value
Range("G" & i).Value = i
For j = 2 To LR
If Range("B" & j).Value = Range("G" & i).Value Then
Range("H" & i).Value = _
Range("H" & i).Value & Range("A" & j).Value & ", "
End If
Next j
If Not IsEmpty(Range("H" & i)) Then
Range("H" & i) = _
Left(Range("H" & i), Len(Range("H" & i)) - 2)
End If
Next i
Range("G1:H1").Insert Shift:=xlDown
End Sub

HTH
Cecil

 

[Stephen Dunn]

Hi again Rob,

you asked the same question a few days ago, and got 3 decent replies. It's
usually best to stay in the same thread, so that others can see what has
already been tried, and you can explain why they were unsatisfactory.

Steve D.