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