Concatenate Puzzler!

[Bretter99]

I have a column of data containing 100 cells, every 5th cell is used for
comments.

I can concatenate them into one summary cell with a line return as follows:

=Concatenate(A1,Char(10),A5,Char(10),Etc.......)

But is there a way that i can skip blanks and if duplicates, only put the
first occurence in?
i.e. if data like this:

Shiny
<Empty>
Furry
Shiny
Furry
Glossy
<Empty>
Matt

My concatenate would return:

Shiny
Furry
Glossy
Matt

Skipping the blank cells, only listing the first occurence of a repeat and
not doing the line return if the cell is blank.

i.e. returning...

Shiny

Furry
Gloss

Matt

Any help would be very much appreciated, im using XP sp2 and office 2000 pro.
I would prefer a written statement for the cell, but VB code i can run off a
macro is fine also.

Thanks

Brett

 

[Rick Rothstein \(MVP - VB\)]

First off, you do not need to use the CONCATENATE function to concatenate
text; you can use an ampersand between text string to join them. Second, you
said the comments were placed every 5th row, but your example showed the 2nd
comment cell was A5 rather than A6... I used A6, A11, A16, etc. for the
series of comment cells. Okay, with that all said, you will not be able to
use a single formula to handle the comments from your 100 rows of data
because the formula needed is too long. It is too bad as you only miss by
one comment cell. There is a way around this problem, but it involves using
two cells, so you will have to make one of them "disappear" (you can put it
in a cell that is "out of sight", or make its font color the same color as
its interior color, or hide its column and/or row). Put the following
formula in the "out of sight" cell (do NOT format it for word wrap)....

=IF(A1<>"",A1,"")&IF(AND(A6<>"",COUNTIF(A1:A6,A6)=1),CHAR(10)&A6,"")&IF(AND(A11<>"",COUNTIF(A1:A11,A11)=1),CHAR(10)&A11,"")&IF(AND(A16<>"",COUNTIF(A1:A16,A16)=1),CHAR(10)&A16,"")&IF(AND(A21<>"",COUNTIF(A1:A21,A21)=1),CHAR(10)&A21,"")&IF(AND(A26<>"",COUNTIF(A1:A26,A26)=1),CHAR(10)&A26,"")&IF(AND(A31<>"",COUNTIF(A1:A31,A31)=1),CHAR(10)&A31,"")&IF(AND(A36<>"",COUNTIF(A1:A36,A36)=1),CHAR(10)&A36,"")&IF(AND(A41<>"",COUNTIF(A1:A41,A41)=1),CHAR(10)&A41,"")&IF(AND(A46<>"",COUNTIF(A1:A46,A46)=1),CHAR(10)&A46,"")&IF(AND(A51<>"",COUNTIF(A1:A51,A51)=1),CHAR(10)&A51,"")&IF(AND(A56<>"",COUNTIF(A1:A56,A56)=1),CHAR(10)&A56,"")&IF(AND(A61<>"",COUNTIF(A1:A61,A61)=1),CHAR(10)&A61,"")&IF(AND(A66<>"",COUNTIF(A1:A66,A66)=1),CHAR(10)&A66,"")&IF(AND(A71<>"",COUNTIF(A1:A71,A71)=1),CHAR(10)&A71,"")&IF(AND(A76<>"",COUNTIF(A1:A76,A76)=1),CHAR(10)&A76,"")&IF(AND(A81<>"",COUNTIF(A1:A81,A81)=1),CHAR(10)&A81,"")&IF(AND(A86<>"",COUNTIF(A1:A86,A86)=1),CHAR(10)&A86,"")&IF(AND(A91<>"",COUNTIF(A1:A91,A91)=1),CHAR(10)&A91,"")

Then, put this formula into the cell you want to display your results in
(and DO format it for word wrap)....

=Z100&IF(AND(A96<>"",COUNTIF(A1:A96,A96)=1),CHAR(10)&A96,"")

Here, I have assumed the "out of sight" cell is Z100 so you will have to
change its reference to the actual cell you are going to use.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Ignore my previous post. While it works, it is not necessary to do it that
way. A slight switch in the logical tests ends up saving enough characters
to make the entire formula fit within a single cell.

Use this formula, by itself, in place of the formula I posted earlier...

=IF(A1<>"",A1,"")&IF(OR(A6="",COUNTIF(A1:A6,A6)>1),"",CHAR(10)&A6)&IF(OR(A11="",COUNTIF(A1:A11,A11)>1),"",CHAR(10)&A11)&IF(OR(A16="",COUNTIF(A1:A16,A16)>1),"",CHAR(10)&A16)&IF(OR(A21="",COUNTIF(A1:A21,A21)>1),"",CHAR(10)&A21)&IF(OR(A26="",COUNTIF(A1:A26,A26)>1),"",CHAR(10)&A26)&IF(OR(A31="",COUNTIF(A1:A31,A31)>1),"",CHAR(10)&A31)&IF(OR(A36="",COUNTIF(A1:A36,A36)>1),"",CHAR(10)&A36)&IF(OR(A41="",COUNTIF(A1:A41,A41)>1),"",CHAR(10)&A41)&IF(OR(A46="",COUNTIF(A1:A46,A46)>1),"",CHAR(10)&A46)&IF(OR(A51="",COUNTIF(A1:A51,A51)>1),"",CHAR(10)&A51)&IF(OR(A56="",COUNTIF(A1:A56,A56)>1),"",CHAR(10)&A56)&IF(OR(A61="",COUNTIF(A1:A61,A61)>1),"",CHAR(10)&A61)&IF(OR(A66="",COUNTIF(A1:A66,A66)>1),"",CHAR(10)&A66)&IF(OR(A71="",COUNTIF(A1:A71,A71)>1),"",CHAR(10)&A71)&IF(OR(A76="",COUNTIF(A1:A76,A76)>1),"",CHAR(10)&A76)&IF(OR(A81="",COUNTIF(A1:A81,A81)>1),"",CHAR(10)&A81)&IF(OR(A86="",COUNTIF(A1:A86,A86)>1),"",CHAR(10)&A86)&IF(OR(A91="",COUNTIF(A1:A91,A91)>1),"",CHAR(10)&A91)&IF(OR(A96="",COUNTIF(A1:A96,A96)>1),"",CHAR(10)&A96)


Rick

 

[Bretter99]

Thanks so much Rick, that works perfectly!

Appreciate the super quick response too!

Best Regards

Brett