Display only unique values in CONCATENATE formula

[Mally]

Thank you to Excel MVP Ron Coderre for the following formula. This is to
used to show if cells A1:A3 are the same then I want to CONCATENATE cells
B1:B3 into
cells D13 and C1:C3 into E1:E3


If Row_1 can contain headings and your data can start in Row_2...
Try this:
D2: =IF($A2<>$A1,B2,"")&IF($A2=$A3,B3,"")&IF($A2=$A4,B4,"")

Some cells contain the same words then these are repeated. Is it possible
for just one of unique values to be displayed. Currently my results look
like this

YesYesYesYesTrafficTraffic

I want it to look like this

Yes Traffic

with a space in between

etc.

Thank you again

 

[Dave Peterson]

If you're existing formula works ok:
=IF($A2<>$A1,B2,"")&IF($A2=$A3,B3,"")&IF($A2=$A4,B4,"")
then change those empty strings "" to a space character " "
=IF($A2<>$A1,B2," ")&IF($A2=$A3,B3," ")&IF($A2=$A4,B4," ")

But this means you could have something like:
Yes Yes Traffic

So wrap it =trim() to get rid of the leading, trailing and multiple embedded
spaces:

=trim(IF($A2<>$A1,B2," ")&IF($A2=$A3,B3," ")&IF($A2=$A4,B4," "))

 

[Ron Coderre]

Using your previously posted data in A1: C7:
Heading1 Heading2 Heading3
1 S A
2 A E
2 S Q
5 L D
8 S T
8 A Z

I hope this formula represents progress...
D2: =TRIM(IF($A2<>$A1,B2&" "," ")&
IF($A2=$A3,IF(AND($A2<>$A1,B3),B3&" ",""),"")&
IF($A2=$A4,IF(AND($A2=$A3,B4<>B3,B4<>B2),B4," "),""))

Copy that formula across and down through E7

With the above data the formulas returns these values in D2:E7:
S A
A S E Q
blank blank
L D
S A T Z

But, using this data:
Heading1 Heading2 Heading3
2 S A
2 A E
2 S Q
5 L D
8 S T
8 A Z

....this is returned:
S A A E Q
blank blank
blank blank
L D
S A T Z

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Ron Coderre]

OOPS! Found a typo...

Try this formula in D2 (and copied across and down through E7):
=TRIM(IF($A2<>$A1,B2&" "," ")&
IF($A2=$A3,IF(AND($A2<>$A1,B2<>B3),B3&" ",""),"")&
IF($A2=$A4,IF(AND($A2=$A3,B4<>B3,B4<>B2),B4," "),""))

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Mally]

Hi Ron

Thanks Ron. That works with the exception of one thing. It works when
there is only 3 or less matching items in column two. When there is more
than 3 items that match then these are displayed on th next line as shown
below.

A B C D E
1 No. aa bb cc dd
2 1 B1 C1 B1 B2 B3 C1 C2 C3
3 1 B2 C2 B4 C4
4 1 B3 C3 B5 C5
5 1 B4 C4 B6 C6
6 1 B5 C5

I've studied your formula and i'm going to see if i can replicate it to
display all matching cells on the same line if this is possible.

Thank you again for all of your help