Text Join of unique strings, based on criteria, avoiding blanks

ellison

Active Member
Joined
Aug 1, 2012
Messages
337
Office Version
  1. 365
Platform
  1. Windows
Good morning, I'm trying (& failing!) to join text, based on a mixture of different formula and have got in a tangle!

Raw info is laid out below in Columns A to C.
Column B = the components that have been bought by which customer (sometimes blank)
Column C = name of customer (sometimes blank)

What I'd like to do is (using table below as an example):

1. Add a column like Col F = Unique Customers (ignoring blanks, A to Z)
2. Add a column like Col G = a "Text join" of the components that they have bought (ignoring blanks), but only one of each type (unique), A to Z.

MakeComponentCustomerCustomerComponents-Joined
LucasWingJJDave KavBulb;Clutch
RegularClutchDave KavDave LassittBonnet
VauxhallWingJJClutch;Wing
VauxhallBonnetPeter WBonnet
RegularDave Lassitt
JaguarBonnetDave Lassitt
Land RoverBonnetPeter W
RegularClutchJJ
GenericBulbDave Kav
JaguarBonnetDave Lassitt
JaguarBonnetDave Lassitt


My raw data is on a sheet of about 1,000 rows.

Thanks for any help you could provide.

Best
Neil
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This should do it.
Due to limitations with dynamic arrays and certain functions (in this case TEXTJOIN) I don't see that it will be possible to make the second column dynamic.
Book1 (version 2).xlsb
ABCDEF
1MakeComponentCustomerCustomerComponents-Joined
2LucasWingJJDave KavBulb;Clutch
3RegularClutchDave KavDave LassittBonnet
4VauxhallWingJJClutch;Wing
5VauxhallBonnetPeter WBonnet
6RegularDave Lassitt
7JaguarBonnetDave Lassitt
8Land RoverBonnetPeter W
9RegularClutchJJ
10GenericBulbDave Kav
11JaguarBonnetDave Lassitt
12JaguarBonnetDave Lassitt
Sheet4
Cell Formulas
RangeFormula
E2:E5E2=SORT(UNIQUE(FILTER(C2:C12,C2:C12<>"")))
F2:F5F2=TEXTJOIN(";",1,SORT(UNIQUE(FILTER($B$2:$B$12,($C$2:$C$12=E2)*($B$2:$B$12<>""),""))))
Dynamic array formulas.
 
Upvote 0
That's absolutely stunning Jason!!!!!
Thank you so much

PS I marked this up as the solution - (hopefully!)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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