Concatenate with no duplicates

B

Bretter99

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
 
B

bj

try
=SUBSTITUTE(CONCATENATE(R48&CHAR(10)&if(countif($R48:S48,S48)>1,"",S48&CHAR(10))&....&IF(or(countif($R48:AC48,AC48>1,AC48=0),"",AC48)),"0"&CHAR(10),"")

puting in the countif function for all of the intermediate cells.
 
B

Bretter99

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,AC4>1,AC4=0),"",AC4)),"0"&CHAR(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)
 
B

Bretter99

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)
 
P

Pete_UK

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(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,AC4>1,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,AC4>1,AC4=0),"",AC4),
"0"&CHAR(10),"")

Hope this helps.

Pete
 
P

Pete_UK

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:w4,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(countif($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
 
B

bj

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,AC4>1,AC4=0),"",AC4)),"0"&CHAR(10),"")
 
R

Ron Rosenfeld

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenate with gap suppression! 4
Portion of concatenate formula to have different color and font fo 1

Top