Concatenate range of cells

[philip1127]

Hi - I'm trying to concatenate a range of cells, (A1:A250). Is there any way to accomplish this without typing (A1&A2&A3...&A250)? Thanks so much.

-Philip

 

[MartinL]

I've been playing with Ricks code from here Flexible Concatenation Function

but not been able to get it to work.

I am rying to populate Contents on the far right based on if there is more than 1 colour in the row then use the value in package type
otherwise
the single colour

here is my example Table1

PACKAGE TYPE

Black

Light Black

Light Light Black

Photo Black

Matte Black

Cyan

Light Cyan

Yellow

Light Yellow

Magenta

Light Magenta

Vivid Magenta

Vivid Light Magenta

Red

Orange

Blue

Green

White

Gloss Optimizer

Color

Metallic Silver

Contents

single

Black

1

Black

multi

Black

Cyan

Light Cyan

Yellow

Magenta

Light Magenta

multi

<tbody>
</tbody>

the code I have tried to use is

=IF(COUNTA(Table1[@[Black]:[Metallic Silver]])-COUNTBLANK(Table1[@[Black]:[Metallic Silver]])>1,
[@[PACKAGE TYPE]],
ConCat(Table1[@[Black]:[Metallic Silver]]))

1. Beware that all the cells, except the header row, are the results of the formula below
=IF(Worksheet!U2="","",Table1[[#Headers],[Light Black]])