| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display only unique values in CONCATENATE formula
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display only unique values in CONCATENATE formula
If you're existing formula works ok:
=IF($A2<$A1,B2,"")&IF($A2=$A3,B3,"")&IF($A2=$A4,B 4,"") 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," ")) Mally wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display only unique values in CONCATENATE formula
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display only unique values in CONCATENATE formula
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 "Ron Coderre" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display only duplicate values and delete UNIQUE Items | Excel Discussion (Misc queries) | |||
| Display Unique Values | Excel Worksheet Functions | |||
| Display Unique Values Question | Excel Worksheet Functions | |||
| count and display unique values | Excel Worksheet Functions | |||
| Modifying a Formula To display only Unique Values | Excel Worksheet Functions | |||
Linear Mode
