Remove Duplicates in a cell Append formula

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
Is there a quick way to remove duplicates in a append formula?


1699284297485.png


When I insert the formula in column E I get duplicates.
=A1 & ", " & B1 & ", " & C1

1699284393433.png


I would like for column E to show Blue,Red for row 1 and Blue,Red,Green for row 2 thus removing the duplicates

Thanks
 
But here are the results I received:
A8 does not have a space after the comma, hence my solution didn't work. Also you solution will only return the first value from each cell, try changing C8 to Green & you will see the Red disappears from the result
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
A8 does not have a space after the comma, hence my solution didn't work. Also you solution will only return the first value from each cell, try changing C8 to Green & you will see the Red disappears from the result
The sample in #6 also did not have a space after the comma.

And thank you for the clarification about the formula.
 
Upvote 0
The sample in #6 also did not have a space after the comma.
Missed that. :(
So the formula should be
Excel Formula:
=TEXTJOIN(", ",,UNIQUE(REDUCE(A8,B8:C8,LAMBDA(x,y,TRIM(HSTACK(TEXTSPLIT(x,","),TEXTSPLIT(y,","))))),1))
 
Upvote 0
I'm unfamiliar with XL2BB

1699302123763.png



Day1Day2Day3Output Without Dupes
RedBlueGreenRed,Blue,Green
RedGreen,Blue,OrangeOrangeRed,Green,Blue,Orange
RedGreenRed,Green
Brown,Brown,GreenBrown,Green
BlackYellow,Lite-GreenBlack,Yellow,Lite-Green
MagentaCyanMagenta,Cyan
 
Upvote 0
Have you tried the formula in post#13?
As dreid1011 pointed out, I had missed the fact there was no space after the comma.
 
Upvote 0
Yes, trying post #13 i receive a #value error

Day1Day2Day3Day1, Day2, Day3
RedBlueGreenRed, Blue, Green
RedGreen,Blue,OrangeOrangeRed, Green, Blue, Orange
RedGreen
#VALUE!​
Brown,Brown,Green
#VALUE!​
BlackYellow,Lite-Green
#VALUE!​
MagentaCyan
#VALUE!​
 
Upvote 0
That's because you have blank cells, which you never showed before.
Try
Excel Formula:
=TEXTJOIN(", ",,UNIQUE(REDUCE(A1,B1:C1,LAMBDA(x,y,TRIM(HSTACK(IF(x="","",TEXTSPLIT(x,",")),IF(y="","",TEXTSPLIT(y,",")))))),1))
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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