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