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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 AColumn HColumn IColumn K
BuyersRef NumbersUnique BuyersCombined Ref Numbers
TonyREf1234TonyREf1234,Hoes 69,Tut Tut
BillIde 456BillIde 456
,Vt67,Hotel 9
SueCan Be AnythingSueCan Be Anything,Ref 221 I want a macro to create K from A and H
SueRef 221
BillVt67
BillHotel 9
TonyHoes 69
TonyTut Tut
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Do you need a macro, this can be done with a formula
Fluff.xlsm
AHIJKLM
1BuyersRef NumbersUnique BuyersCombined Ref NumbersCombined Ref Numbers
2TonyREf1234TonyREf1234, Hoes 69, Tut TutREf1234, Hoes 69, Tut Tut
3BillIde 456BillIde 456, Vt67, Hotel 9Ide 456, Vt67, Hotel 9
4SueCan Be AnythingSueCan Be Anything, Ref 221Can Be Anything, Ref 221
5SueRef 221
6BillVt67
7BillHotel 9
8TonyHoes 69
9TonyTut Tut
10
Main
Cell Formulas
RangeFormula
M1:M4M1=REDUCE("Combined Ref Numbers",J2#,LAMBDA(a,b,VSTACK(a,TEXTJOIN(", ",,FILTER(H2:H5000,A2:A5000=b)))))
J2:J4J2=UNIQUE(FILTER(A2:A5000,A2:A5000<>""))
K2:K4K2=TEXTJOIN(", ",,FILTER(H2:H5000,A2:A5000=J2))
Dynamic array formulas.
 
Upvote 0
Solution
Thanks, Fluff, I assumed a macro would be needed but this is perfect :)
 
Upvote 0
You're welcome & thanks fro the feedback.
 
Upvote 0
Or , you can create a function

VBA Code:
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

1666612479681.png
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top