Concatenate rows of data into a single cell based on a Unique ID

[Sparkee]

Hi, I have a report coming out of a database that lists my data in rows, resulting in multiple lines for each person shown below. I would like to concatenate the data in the fruit and extra fruit columns into a single row for each person. I have had a play with TEXTJOIN and have ended up using UniqConcat. I have the below VBA code, and this is working to concatenate the rows, but I need it to re-set each time the Unique ID changes. I was hoping that one of you amazing people could assist me with this?

Function UniqConcat(rng As Range, str As String)
Dim ucoll As New Collection, Value As Variant, temp As String

On Error Resume Next
For Each Value In rng
    If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

For Each Value In ucoll
    temp = temp & Value & str
Next Value

temp = Mid(temp, 1, Len(temp) - Len(str))
UniqConcat = temp

End Function

]

My I want my spreadsheet looks like this so that I can then remove the duplicates and I am then left with one line per person.

Last Name	First Name	ApplicationType	ExpiryDate	Facility	Fruit	Extra Fruit	Unique ID	Result	Extra Result
Smith	James	New	31/12/2020​	White House	Apple	Chicken	803​	Apple, Orange	Chicken, Beef, Lamb
Smith	James	New	31/12/2020​	White House	Apple	Beef	803​	Apple, Orange	Chicken, Beef, Lamb
Smith	James	New	31/12/2020​	White House	Apple	Lamb	803​	Apple, Orange	Chicken, Beef, Lamb
Smith	James	New	31/12/2020​	White House	Orange	Chicken	803​	Apple, Orange	Chicken, Beef, Lamb
Smith	James	New	31/12/2020​	White House	Orange	Beef	803​	Apple, Orange	Chicken, Beef, Lamb
Smith	James	New	31/12/2020​	White House	Orange	Lamb	803​	Apple, Orange	Chicken, Beef, Lamb
Jones	Lucy	New	31/12/2020​	White House	Plum		804​	Plum, Mango, Pear
Jones	Lucy	New	31/12/2020​	White House	Mango		804​	Plum, Mango, Pear
Jones	Lucy	New	31/12/2020​	White House	Pear		804​	Plum, Mango, Pear

 

[alansidman]

Using the approach shown on this site, Excel: Reporting Text in a Pivot Table - Strategic Finance

Here is my result

Book3
	A	B	C	D	E	F
3	Last Name	First Name	ExpiryDate	Facility	FruitCombo	ExtraCombo
4	Jones	Lucy	31/12/2020	White House	Plum,Mango,Pear	,,
5	Smith	James	31/12/2020	White House	Apple,Apple,Apple,Orange,Orange,Orange	Chicken,Beef,Lamb,Chicken,Beef,Lamb
Sheet2

 

[alansidman]

Revised

Book3.xlsx
	A	B	C	D	E
1
2
3	Last Name	First Name	ExpiryDate	Fruit2	ExtraFruit2
4	Jones
5		Lucy
6			31/12/2020	Plum,Mango,Pear
7	Smith
8		James
9			31/12/2020	Apple,Orange	Chicken,Beef,Lamb
10
11
Sheet3

used the amended formula to create new measures:

You can remove the duplicates by changing =CONCATENATEX(Table1,[Fruit],”, “) to =CONCATENATEX(Values(Table1[Fruit]),Table1[Fruit],”, “).

Of course you will have to create a new measure for Extra Fruit following the same methodology

 

[Sparkee]

Ok, I have done it this way and it is working well. A bit time consuming but one it is set up, it will be easy to use.

thanks Alan.

 

[Peter_SSs]

@Sparkee
What Excel version are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using without having to ask the above question. The best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Sparkee]

@Peter_SSs I am using Excel Office 365 on Windows.

Thank you, I have updated my account details.

 

[Peter_SSs]

Thanks for the profile update.

So, if your version of Excel 365 has the FILTER & UNIQUE functions (not everybody does yet but if not, you should have it soon) you may be able to use something like this that does not require you to subsequently remove duplicates.
The formula in column J needs to be entered in J2 only. The other ID values will automatically spill down to the other rows as required.
The formula in K2 is copied across, and down as far as you might ever need.

Sparkee 2020-07-14 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Last Name	First Name	ApplicationType	ExpiryDate	Facility	Fruit	Extra Fruit	Unique ID		Unique ID	Result	Extra Result
2	Smith	James	New	31/12/2020	White House	Apple	Chicken	803		803	Apple, Orange	Chicken, Beef, Lamb
3	Smith	James	New	31/12/2020	White House	Apple	Beef	803		804	Plum, Mango, Pear
4	Smith	James	New	31/12/2020	White House	Apple	Lamb	803
5	Smith	James	New	31/12/2020	White House	Orange	Chicken	803
6	Smith	James	New	31/12/2020	White House	Orange	Beef	803
7	Smith	James	New	31/12/2020	White House	Orange	Lamb	803
8	Jones	Lucy	New	31/12/2020	White House	Plum		804
9	Jones	Lucy	New	31/12/2020	White House	Mango		804
10	Jones	Lucy	New	31/12/2020	White House	Pear		804
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=UNIQUE(H2:H10)
K2:L10	K2	=TEXTJOIN(", ",1,UNIQUE(FILTER(F$2:F$10,$H$2:$H$10=$J2,"")))
Dynamic array formulas.

 

[Sparkee]

Oh fantastic Peter, that works and is exactly what I needed. Thank you

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.