| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate with no duplicates
Hi all,
Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
try
=SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
Bj
I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
Too many layers of nesting? Which version of Excel are you using?
-- David Biddulph "Bretter99" wrote in message ... Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
Hi David, I'm using Excel 2000, i'm wondering wether the problem is just the
end part, not quite sure, reading it, it looks ok apart from the end, something doesnt look quite right, too many operands or something?...... (The line returns were just to make the reading easier for you rather than if it was one long text string as it is in the cell) "David Biddulph" wrote: Too many layers of nesting? Which version of Excel are you using? -- David Biddulph "Bretter99" wrote in message ... Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
For Excel 2003 and earlier you are limited to 7 layers of nesting of
functions: http://office.microsoft.com/en-us/ex...992911033.aspx You seem to have at least twice that. -- David Biddulph "Bretter99" wrote in message ... Hi David, I'm using Excel 2000, i'm wondering wether the problem is just the end part, not quite sure, reading it, it looks ok apart from the end, something doesnt look quite right, too many operands or something?...... (The line returns were just to make the reading easier for you rather than if it was one long text string as it is in the cell) "David Biddulph" wrote: Too many layers of nesting? Which version of Excel are you using? -- David Biddulph "Bretter99" wrote in message ... Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
The problem is that you have missed a bracket after each CHAR(10) in
order to close the IFs - you need something like this: =SUBSTITUTE(R4&CHAR(10)&if(countif($R4:S4,S4)1,"" ,S4&CHAR(10))&if(countif($R4:t4,t4)1,"",T4&CHAR(1 0))&if(countif($R4:u4,u4)1,"",U4&CHAR(10))&if(cou ntif($R4:v4,v4)1,"",V4&CHAR(10))&if(countif($R4:w 4,w4)1,"",W4&CHAR(10))&if(countif($R4:x4,x4)1,"" ,X4&CHAR(10))&if(countif($R4:y4,y4)1,"",Y4&CHAR(1 0))&if(countif($R4:z4,z4)1,"",Z4&CHAR(10))&if(cou ntif($R4:aa4,aa4)1,"",AA4&CHAR(10))&if(countif($R 4:ab4,ab4)1,"",AB4&CHAR(10))&IF(or(countif($R4:AC 4,AC41,AC4=0),"",AC4),"0"&CHAR(10),"") All one formula - it might be easier to follow the logic if I show it like this, with manual line breaks: =SUBSTITUTE(R4&CHAR(10) &if(countif($R4:S4,S4)1,"",S4&CHAR(10)) &if(countif($R4:t4,t4)1,"",T4&CHAR(10)) &if(countif($R4:u4,u4)1,"",U4&CHAR(10)) &if(countif($R4:v4,v4)1,"",V4&CHAR(10)) &if(countif($R4:w4,w4)1,"",W4&CHAR(10)) &if(countif($R4:x4,x4)1,"",X4&CHAR(10)) &if(countif($R4:y4,y4)1,"",Y4&CHAR(10)) &if(countif($R4:z4,z4)1,"",Z4&CHAR(10)) &if(countif($R4:aa4,aa4)1,"",AA4&CHAR(10)) &if(countif($R4:ab4,ab4)1,"",AB4&CHAR(10)) &IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4), "0"&CHAR(10),"") Hope this helps. Pete On Jul 26, 8:52 am, Bretter99 wrote: Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR*(10))&....&IF(or(countif ($R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10)," "*) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)*&V48&CHAR(10)&W48&CHAR(1 0)&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHA R*(10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR( 10),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
Sorry, bracket missing from the final COUNTIF term - it should be:
&IF(or(countif($R4:AC4,AC4)1,AC4=0),"",AC4), Also, not sure if the 0 here should be "0" - depends on what you return in your original formulae. The complete formula is now: =SUBSTITUTE(R4&CHAR(10)&if(countif($R4:S4,S4)1,"" ,S4&CHAR(10))&if(countif(* $R4:t4,t4)1,"",T4&CHAR(10))&if(countif($R4:u4,u4) 1,"",U4&CHAR(10))&if(cou* ntif($R4:v4,v4)1,"",V4&CHAR(10))&if(countif($R4:w 4,w4)1,"",W4&CHAR(10))&i* f(countif($R4:x4,x4)1,"",X4&CHAR(10))&if(countif( $R4:y4,y4)1,"",Y4&CHAR(1* 0))&if(countif($R4:z4,z4)1,"",Z4&CHAR(10))&if(cou ntif($R4:aa4,aa4)1,"",AA* 4&CHAR(10))&if(countif($R4:ab4,ab4)1,"",AB4&CHAR( 10))&IF(or(countif($R4:AC* 4,AC4)1,AC4=0),"",AC4),"0"&CHAR(10),"") Be wary of spurious line breaks if you copy/paste from the newgroups. Hope this helps. Pete On Jul 26, 11:57 am, Pete_UK wrote: The problem is that you have missed a bracket after each CHAR(10) in order to close the IFs - you need something like this: =SUBSTITUTE(R4&CHAR(10)&if(countif($R4:S4,S4)1,"" ,S4&CHAR(10))&if(countif(*$R4:t4,t4)1,"",T4&CHAR( 10))&if(countif($R4:u4,u4)1,"",U4&CHAR(10))&if(co u*ntif($R4:v4,v4)1,"",V4&CHAR(10))&if(countif($R4 :w4,w4)1,"",W4&CHAR(10))&i*f(countif($R4:x4,x4)1 ,"",X4&CHAR(10))&if(countif($R4:y4,y4)1,"",Y4&CHA R(1*0))&if(countif($R4:z4,z4)1,"",Z4&CHAR(10))&if (countif($R4:aa4,aa4)1,"",AA*4&CHAR(10))&if(count if($R4:ab4,ab4)1,"",AB4&CHAR(10))&IF(or(countif($ R4:AC*4,AC41,AC4=0),"",AC4),"0"&CHAR(10),"") All one formula - it might be easier to follow the logic if I show it like this, with manual line breaks: =SUBSTITUTE(R4&CHAR(10) &if(countif($R4:S4,S4)1,"",S4&CHAR(10)) &if(countif($R4:t4,t4)1,"",T4&CHAR(10)) &if(countif($R4:u4,u4)1,"",U4&CHAR(10)) &if(countif($R4:v4,v4)1,"",V4&CHAR(10)) &if(countif($R4:w4,w4)1,"",W4&CHAR(10)) &if(countif($R4:x4,x4)1,"",X4&CHAR(10)) &if(countif($R4:y4,y4)1,"",Y4&CHAR(10)) &if(countif($R4:z4,z4)1,"",Z4&CHAR(10)) &if(countif($R4:aa4,aa4)1,"",AA4&CHAR(10)) &if(countif($R4:ab4,ab4)1,"",AB4&CHAR(10)) &IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4), "0"&CHAR(10),"") Hope this helps. Pete On Jul 26, 8:52 am, Bretter99 wrote: Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR**(10))&....&IF(or(counti f($R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10), "*"*) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)**&V48&CHAR(10)&W48&CHAR( 10)&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CH A*R*(10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHA R(10),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
you have the if statements nested and they should not be
=SUBSTITUTE(CONCATENATE(R4&CHAR(10) &if(countif($R4:S4,S4)1,"",S4 &CHAR(10)) &if(countif($R4:t4,t4)1,"",T4&CHAR(10)) &if(countif($R4:u4,u4)1,"",U4&CHAR(10)) &if(countif($R4:v4,v4)1,"",V4&CHAR(10)) &if(countif($R4:w4,w4)1,"",W4&CHAR(10)) &if(countif($R4:x4,x4)1,"",X4&CHAR(10)) &if(countif($R4:y4,y4)1,"",Y4&CHAR(10)) &if(countif($R4:z4,z4)1,"",Z4&CHAR(10)) &if(countif($R4:aa4,aa4)1,"",AA4&CHAR(10)) &if(countif($R4:ab4,ab4)1,"",AB4&CHAR(10)) &if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),"") "Bretter99" wrote: Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
I knew I should have gone two sections , but got lazy, I appologize
"bj" wrote: you have the if statements nested and they should not be =SUBSTITUTE(CONCATENATE(R4&CHAR(10) &if(countif($R4:S4,S4)1,"",S4 &CHAR(10)) &if(countif($R4:t4,t4)1,"",T4&CHAR(10)) &if(countif($R4:u4,u4)1,"",U4&CHAR(10)) &if(countif($R4:v4,v4)1,"",V4&CHAR(10)) &if(countif($R4:w4,w4)1,"",W4&CHAR(10)) &if(countif($R4:x4,x4)1,"",X4&CHAR(10)) &if(countif($R4:y4,y4)1,"",Y4&CHAR(10)) &if(countif($R4:z4,z4)1,"",Z4&CHAR(10)) &if(countif($R4:aa4,aa4)1,"",AA4&CHAR(10)) &if(countif($R4:ab4,ab4)1,"",AB4&CHAR(10)) &if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),"") "Bretter99" wrote: Bj I now have: =SUBSTITUTE(CONCATENATE(R4&CHAR(10)&if(countif($R4 :S4,S4)1,"", S4&CHAR(10)&if(countif($R4:t4,t4)1,"", T4&CHAR(10)&if(countif($R4:u4,u4)1,"", U4&CHAR(10)&if(countif($R4:v4,v4)1,"", V4&CHAR(10)&if(countif($R4:w4,w4)1,"", W4&CHAR(10)&if(countif($R4:x4,x4)1,"", X4&CHAR(10)&if(countif($R4:y4,y4)1,"", Y4&CHAR(10)&if(countif($R4:z4,z4)1,"", Z4&CHAR(10)&if(countif($R4:aa4,aa4)1,"", AA4&CHAR(10)&if(countif($R4:ab4,ab4)1,"", AB4&CHAR(10)&if(countif($R4:ac4,ac4)1,"", IF(or(countif($R4:AC4,AC41,AC4=0),"",AC4)),"0"&CH AR(10),""))))))))))))) but when i press return i get a message saying "your formula has an error", i cant see what it is, any suggestions? (Just to restress, the cell range is R4-AC4 inclusive, this formula being in cell AD4 and needing to be dragged down 60 rows) "bj" wrote: try =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R 48:S48,S48)1,"",S48&CHAR(10))&....&IF(or(countif( $R48:AC48,AC481,AC48=0),"",AC48)),"0"&CHAR(10),"" ) puting in the countif function for all of the intermediate cells. "Bretter99" wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10)& T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(10 )&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHAR (10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(10 ),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate with no duplicates
On Wed, 25 Jul 2007 08:26:00 -0700, Bretter99
wrote: Hi all, Im using the below formula to pull together a load of cells into one text string with a line return between each one and skipping all cells with a "0" in (Thanks to Toppers!), now i'm wondering wether i can actually avoid duplicates at the same time: =SUBSTITUTE(CONCATENATE(R48&CHAR(10)&S48&CHAR(10) &T48&CHAR(10)&U48&CHAR(10)&V48&CHAR(10)&W48&CHAR(1 0)&X48&CHAR(10)&Y48&CHAR(10)&Z48&CHAR(10)&AA48&CHA R(10)&AB48&CHAR(10)&IF(AC48=0,"",AC48)),"0"&CHAR(1 0),"") Say the list looks something like.. Red 0 Blue 0 Green Green 0 0 Green Green Black i just want... Red Blue Green Black I know i ask a lot but this could save hours a week of manual sorting and reporting. Thanks Brett What about a UDF? <alt-F11 opens the VB Editor Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use the formula: =concatnondups(rg) where rg represents the range of cells you wish to concatenate. ========================================== Option Explicit Function ConcatNonDups(rg) As String Dim c As Range For Each c In rg If c.Text < 0 And _ InStr(1, ConcatNonDups, c.Text, vbTextCompare) = 0 Then ConcatNonDups = ConcatNonDups & c.Text & vbLf End If Next c ConcatNonDups = Left(ConcatNonDups, Len(ConcatNonDups) - 1) End Function =========================================== --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicates | Excel Worksheet Functions | |||
| Find Duplicates & Concatenate (cpm) | Excel Discussion (Misc queries) | |||
| I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
| Duplicates | Excel Discussion (Misc queries) | |||
| Duplicates | Excel Discussion (Misc queries) | |||
Linear Mode
