CODEWITHSUNDEEP
excelexcel-formula
Jason
Jason

Reputation: 47

TextJoin: How to remove duplicate values?

I have the following table of data:

enter image description here

In C2, I then input the following formula =TEXTJOIN(", ",TRUE,IF($B$2:$B$7=$B2,$A$2:$A$7,"")) which returns the following:

enter image description here

However, I want the formula to ignore any duplicates and just return 1, 30

I've had a peruse, and looked a similarly asked questions on here, but i am unable to get anything to work

EDIT

I have partially resolved this with the following code:

=TEXTJOIN(", ",TRUE,IF($B$2:$B$13=$B2,IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($A$2:$A$13)-ROW($A$2)+1,$A$2:$A$13,""),""))

However, if the ID code changes and an income code, previously populated under a different ID, appears it will just return blank:

enter image description here

Many thanks

Upvotes: 0

Views: 1854

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Or, try this array formula (Ctrl+Shift+Enter) instead :

=TEXTJOIN(", ",TRUE,IF($B$2:$B$13=$B2,IF(MATCH($A$2:$A$13&$B$2:$B$13,$A$2:$A$13&$B$2:$B$13,0)=ROW($A$2:$A$13)-ROW($A$2)+1,$A$2:$A$13,""),""))

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Applies to O365 with the UNIQUE function

try:

=TEXTJOIN(",",TRUE,UNIQUE(IF($B$2:$B$7=$B2,IF($A$2:$A$7="","",$A$2:$A$7),"")))

enter image description here

Upvotes: 1

Related Questions