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 -