ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Concatenate with no duplicates (https://www.excelbanter.com/excel-discussion-misc-queries/151662-concatenate-no-duplicates.html)

Bretter99
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

bj
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

Bretter99
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

David Biddulph[_2_]
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



Bretter99
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




David Biddulph[_2_]
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

Pete_UK
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 -

Pete_UK
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 -

bj
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

bj
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

Ron Rosenfeld
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


All times are GMT +1. The time now is 03:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com