Macro to combine all Ref's into one column for my Unique List

[tonywatsonhelp]

Hi Everyone,

I really need some help here please,
I have a sheet that contains sales in columns A to H

Column A is the list of Buyers names
now in Column J I have made a list of my Buyers from "A" using the "Unique" formula (i.e. "=unique(A2:A5000")")
this is great but I need each line's ref number. that's in column H

so I need a macro that when I run it takes each name in Column J2:J the last row, and bring all the data that's in column H for this name

so if the Buyer was Tony,

then column A will have at least one tony, so find each tony in range A2:A5000 and whatever in column H put into I using a comma to separate each bit of data. like below!
please help if you can i'm really stuck and need this for the morning

Column A		Column H				Column I	Column K
Buyers		Ref Numbers				Unique Buyers	Combined Ref Numbers
Tony		REf1234				Tony	REf1234,Hoes 69,Tut Tut
Bill		Ide 456				Bill	Ide 456 , Vt67,Hotel 9
Sue		Can Be Anything				Sue	Can Be Anything,Ref 221	I want a macro to create K from A and H
Sue		Ref 221
Bill		Vt67
Bill		Hotel 9
Tony		Hoes 69
Tony		Tut Tut

 

[Fluff]

Do you need a macro, this can be done with a formula

Fluff.xlsm
	A							H	I	J	K	L	M
1	Buyers							Ref Numbers		Unique Buyers	Combined Ref Numbers		Combined Ref Numbers
2	Tony							REf1234		Tony	REf1234, Hoes 69, Tut Tut		REf1234, Hoes 69, Tut Tut
3	Bill							Ide 456		Bill	Ide 456, Vt67, Hotel 9		Ide 456, Vt67, Hotel 9
4	Sue							Can Be Anything		Sue	Can Be Anything, Ref 221		Can Be Anything, Ref 221
5	Sue							Ref 221
6	Bill							Vt67
7	Bill							Hotel 9
8	Tony							Hoes 69
9	Tony							Tut Tut
10
Main

Cell Formulas
Range	Formula
M1:M4	M1	=REDUCE("Combined Ref Numbers",J2#,LAMBDA(a,b,VSTACK(a,TEXTJOIN(", ",,FILTER(H2:H5000,A2:A5000=b)))))
J2:J4	J2	=UNIQUE(FILTER(A2:A5000,A2:A5000<>""))
K2:K4	K2	=TEXTJOIN(", ",,FILTER(H2:H5000,A2:A5000=J2))
Dynamic array formulas.

 

[tonywatsonhelp]

Thanks, Fluff, I assumed a macro would be needed but this is perfect

 

[Fluff]

You're welcome & thanks fro the feedback.

 

[Flaiban]

Or , you can create a function

Function TonyWatson(LookupRange As Range, LookupVal As Variant, ConcatRange As Range, Optional Separator As String = ",") As Variant

Dim i As Integer

Dim Result As String

On Error Resume Next

If LookupRange.Count <> ConcatRange.Count Then

    TonyWatson = CVErr(xlErrRef)

    Exit Function

End If

For i = 1 To LookupRange.Count

    If LookupRange.Cells(i).Value = LookupVal Then

        Result = Result & Separator & ConcatRange.Cells(i).Value

    End If

Next i

If Result <> "" Then

    Result = VBA.Mid(Result, VBA.Len(Separator) + 1)

End If

TonyWatson = Result

Exit Function

End Function