Prev: retrieve data from different workbook
Next: If count
From: Pierre on 8 Feb 2010 20:11
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
From: "Bernie Deitrick" deitbe on 8 Feb 2010 22:26
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" <Pierre(a)discussions.microsoft.com> wrote in message
news:2173FEA2-A40C-44C7-9D3A-2B9D03B686AF(a)microsoft.com...
> 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

From: Pierre on 9 Feb 2010 14:48
Bernie, you are indeed a gorgeous man! (in a platonic, manly sort of way :) )

Cheers.

Pierre



"Bernie Deitrick" wrote:

> 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" <Pierre(a)discussions.microsoft.com> wrote in message
> news:2173FEA2-A40C-44C7-9D3A-2B9D03B686AF(a)microsoft.com...
> > 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
>
> .
>
 | 
Pages: 1
Prev: retrieve data from different workbook
Next: If count