Display only unique values in CONCATENATE formula
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)
"Ron Coderre" wrote in message
...
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)
"Mally" wrote in message
...
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 D1:D3 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,B 4,"")
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
|