Getting a formula to do the following:

[Pierre]

Hi there, I need a bit of a hand writing a formula.

I have a column of numbers that has duplicates and triplicates in it; I'm
trying to concatenate the data conditionally - something like this:

if(a2=a3), concatenate(c2:c3, d2:d3 ...) then select and delete row a3.
It then needs to apply the same formula, repeating the same process, or
stepping down a row if a2<>a3...

Any help appreciated.

Cheers

 

[Bernie Deitrick]

Pierre,

In a cell on row 2, enter the formula

=IF(AND($A2<>$A1,$A2<>$A3),C2,IF(AND($A2<>$A1,$A2=$A3),C2&C3&IF($A3=$A4,C4,""),""))

and copy down to match your column A, then across for as many columns as you
needed concatenated. Then copy all those formulas, paste values, and delete
the original data (except column A). Then select the first of your new data
columns and use Edit / Go to... special blanks Ok and delete those rows.

This checks for singletons, doubles, and triples only.

HTH,
Bernie
MS Excel MVP

 

[Pierre]

Bernie, you are indeed a gorgeous man! (in a platonic, manly sort of way )

Cheers.

Pierre