Getting a formula to do the following:

  • Thread starter Thread starter Pierre
  • Start date Start date
P

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
 
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
 
Bernie, you are indeed a gorgeous man! (in a platonic, manly sort of way :) )

Cheers.

Pierre
 
Back
Top