duplicate data in columns

Evening All
Not sure if this is possible I have a table of data:
desc cost 1 cost 2 cost 3 cost 4 etc
XYZ £10 £15 £10 £20

can I perform a process by removing all the duplicate costs for each
description just leaving desc and the unique costs associated with them.
I have ASAP utilities add-in with can empty duplicates in selection works
great, but only when the data is in a column. So I need something to do the
same but across a large number of rows.
Hope this is understandable
TIA
Ajay

Looking at your example, do you mean you want to clear (erase) all the 10
pound entries in a row except the first one? And the same would apply for
each numerical entry in a row? This would make that row look like this:
XYZ 10 15 blank cell 20
Is that correct? HTH Otto
"Ajay" <Ajay@discussions.microsoft.com> wrote in message
news:771EF596-47CA-450E-BE77-AADC809758DA@microsoft.com...
> Evening All
> Not sure if this is possible I have a table of data:
> desc cost 1 cost 2 cost 3 cost 4 etc
> XYZ £10 £15 £10 £20
>
> can I perform a process by removing all the duplicate costs for each
> description just leaving desc and the unique costs associated with them.
> I have ASAP utilities add-in with can empty duplicates in selection works
> great, but only when the data is in a column. So I need something to do
> the
> same but across a large number of rows.
> Hope this is understandable
> TIA
> Ajay

Hi,

Let's assume that Sheet 1 contains the original data: [columns headers (ie.,
"Desc", "Cost 1", "Cost 2",...) in Row 1 (say, A1 .... J1), descriptions in
A2:A101, and costs in B2:B101,...J2:J101).

If you want to maintain the cell positions and just want to empty out
duplicate entries,
In Sheet 2, copy the header row (A1:J1). and Description column (A2:A101);
In B2 enter the following formula:

=IF(Sheet1!B2="","",IF(COUNTIF(Sheet1!$B2:$J2,Sheet1!B2)=COUNTIF(Sheet1!B2:$J2,Sheet1!B2),Sheet1!B2,""))

and fill-in the formula across the entire data range (B2:J101)

If, on the other hand, you want to arrange the unique entries in say
ascending order across columns in each row, use the following formula in B2
of Sheet 2(and fill-in the formula acorss the data range).

=IF(OR(A2=MAX(Sheet1!$B2:$J2),A2=""),"",SMALL(Sheet1!$B2:$J2,COUNTIF(Sheet1!$B2:$J2,"<="&A2)+1))

Regards,
B. R. Ramachandran


"Ajay" wrote:

> Evening All
> Not sure if this is possible I have a table of data:
> desc cost 1 cost 2 cost 3 cost 4 etc
> XYZ £10 £15 £10 £20
>
> can I perform a process by removing all the duplicate costs for each
> description just leaving desc and the unique costs associated with them.
> I have ASAP utilities add-in with can empty duplicates in selection works
> great, but only when the data is in a column. So I need something to do the
> same but across a large number of rows.
> Hope this is understandable
> TIA
> Ajay

Morning Otto
yes that is exactly what I want to do
TIA ajay

"Otto Moehrbach" wrote:

> Looking at your example, do you mean you want to clear (erase) all the 10
> pound entries in a row except the first one? And the same would apply for
> each numerical entry in a row? This would make that row look like this:
> XYZ 10 15 blank cell 20
> Is that correct? HTH Otto
> "Ajay" <Ajay@discussions.microsoft.com> wrote in message
> news:771EF596-47CA-450E-BE77-AADC809758DA@microsoft.com...
> > Evening All
> > Not sure if this is possible I have a table of data:
> > desc cost 1 cost 2 cost 3 cost 4 etc
> > XYZ £10 £15 £10 £20
> >
> > can I perform a process by removing all the duplicate costs for each
> > description just leaving desc and the unique costs associated with them.
> > I have ASAP utilities add-in with can empty duplicates in selection works
> > great, but only when the data is in a column. So I need something to do
> > the
> > same but across a large number of rows.
> > Hope this is understandable
> > TIA
> > Ajay
>
>
>

Many thanks for this will give it a go
will let you know how successful I am
Ajay

"B. R.Ramachandran" wrote:

>
> Hi,
>
> Let's assume that Sheet 1 contains the original data: [columns headers (ie.,
> "Desc", "Cost 1", "Cost 2",...) in Row 1 (say, A1 .... J1), descriptions in
> A2:A101, and costs in B2:B101,...J2:J101).
>
> If you want to maintain the cell positions and just want to empty out
> duplicate entries,
> In Sheet 2, copy the header row (A1:J1). and Description column (A2:A101);
> In B2 enter the following formula:
>
> =IF(Sheet1!B2="","",IF(COUNTIF(Sheet1!$B2:$J2,Sheet1!B2)=COUNTIF(Sheet1!B2:$J2,Sheet1!B2),Sheet1!B2,""))
>
> and fill-in the formula across the entire data range (B2:J101)
>
> If, on the other hand, you want to arrange the unique entries in say
> ascending order across columns in each row, use the following formula in B2
> of Sheet 2(and fill-in the formula acorss the data range).
>
> =IF(OR(A2=MAX(Sheet1!$B2:$J2),A2=""),"",SMALL(Sheet1!$B2:$J2,COUNTIF(Sheet1!$B2:$J2,"<="&A2)+1))
>
> Regards,
> B. R. Ramachandran
>
>
> "Ajay" wrote:
>
> > Evening All
> > Not sure if this is possible I have a table of data:
> > desc cost 1 cost 2 cost 3 cost 4 etc
> > XYZ £10 £15 £10 £20
> >
> > can I perform a process by removing all the duplicate costs for each
> > description just leaving desc and the unique costs associated with them.
> > I have ASAP utilities add-in with can empty duplicates in selection works
> > great, but only when the data is in a column. So I need something to do the
> > same but across a large number of rows.
> > Hope this is understandable
> > TIA
> > Ajay

Morning B.R. Ramachandran
Quick question re the formula on the second part is it A" that goes in the
formula?
2ndly when I remove the duplicate entries using the first formula and use
counta to check number of entries left It counts the blank cells?
Any ideas wot I am doing wrong!
TIA
Ajay

"B. R.Ramachandran" wrote:

>
> Hi,
>
> Let's assume that Sheet 1 contains the original data: [columns headers (ie.,
> "Desc", "Cost 1", "Cost 2",...) in Row 1 (say, A1 .... J1), descriptions in
> A2:A101, and costs in B2:B101,...J2:J101).
>
> If you want to maintain the cell positions and just want to empty out
> duplicate entries,
> In Sheet 2, copy the header row (A1:J1). and Description column (A2:A101);
> In B2 enter the following formula:
>
> =IF(Sheet1!B2="","",IF(COUNTIF(Sheet1!$B2:$J2,Sheet1!B2)=COUNTIF(Sheet1!B2:$J2,Sheet1!B2),Sheet1!B2,""))
>
> and fill-in the formula across the entire data range (B2:J101)
>
> If, on the other hand, you want to arrange the unique entries in say
> ascending order across columns in each row, use the following formula in B2
> of Sheet 2(and fill-in the formula acorss the data range).
>
> =IF(OR(A2=MAX(Sheet1!$B2:$J2),A2=""),"",SMALL(Sheet1!$B2:$J2,COUNTIF(Sheet1!$B2:$J2,"<="&A2)+1))
>
> Regards,
> B. R. Ramachandran
>
>
> "Ajay" wrote:
>
> > Evening All
> > Not sure if this is possible I have a table of data:
> > desc cost 1 cost 2 cost 3 cost 4 etc
> > XYZ £10 £15 £10 £20
> >
> > can I perform a process by removing all the duplicate costs for each
> > description just leaving desc and the unique costs associated with them.
> > I have ASAP utilities add-in with can empty duplicates in selection works
> > great, but only when the data is in a column. So I need something to do the
> > same but across a large number of rows.
> > Hope this is understandable
> > TIA
> > Ajay

Hi Ajay,

I didn't understand your first question. Are you talking about the second
formula I suggested (which would arrange unique entries in ascending order
across each row after removing duplicate entries)? The formula should go to
B2. Then fill-in the formula across the columns in Row 2 (i.e., B2 to J2);
then select B2:J2, and fill-in the formula(s) down to the last row (say
B101:J101). In doing so, the formulas in in B2, C2, ..... and J2 get
extended till the last row of the corresponding columns.

Your 2nd question:
To check the number of entries left after removing duplicates, use
=COUNT(range), not =COUNTA(range).
For example, for row 2, it would be =COUNT(B2:J2)

Regards,
B. R. Ramachandran





"Ajay" wrote:

> Morning B.R. Ramachandran
> Quick question re the formula on the second part is it A" that goes in the
> formula?
> 2ndly when I remove the duplicate entries using the first formula and use
> counta to check number of entries left It counts the blank cells?
> Any ideas wot I am doing wrong!
> TIA
> Ajay
>
> "B. R.Ramachandran" wrote:
>
> >
> > Hi,
> >
> > Let's assume that Sheet 1 contains the original data: [columns headers (ie.,
> > "Desc", "Cost 1", "Cost 2",...) in Row 1 (say, A1 .... J1), descriptions in
> > A2:A101, and costs in B2:B101,...J2:J101).
> >
> > If you want to maintain the cell positions and just want to empty out
> > duplicate entries,
> > In Sheet 2, copy the header row (A1:J1). and Description column (A2:A101);
> > In B2 enter the following formula:
> >
> > =IF(Sheet1!B2="","",IF(COUNTIF(Sheet1!$B2:$J2,Sheet1!B2)=COUNTIF(Sheet1!B2:$J2,Sheet1!B2),Sheet1!B2,""))
> >
> > and fill-in the formula across the entire data range (B2:J101)
> >
> > If, on the other hand, you want to arrange the unique entries in say
> > ascending order across columns in each row, use the following formula in B2
> > of Sheet 2(and fill-in the formula acorss the data range).
> >
> > =IF(OR(A2=MAX(Sheet1!$B2:$J2),A2=""),"",SMALL(Sheet1!$B2:$J2,COUNTIF(Sheet1!$B2:$J2,"<="&A2)+1))
> >
> > Regards,
> > B. R. Ramachandran
> >
> >
> > "Ajay" wrote:
> >
> > > Evening All
> > > Not sure if this is possible I have a table of data:
> > > desc cost 1 cost 2 cost 3 cost 4 etc
> > > XYZ £10 £15 £10 £20
> > >
> > > can I perform a process by removing all the duplicate costs for each
> > > description just leaving desc and the unique costs associated with them.
> > > I have ASAP utilities add-in with can empty duplicates in selection works
> > > great, but only when the data is in a column. So I need something to do the
> > > same but across a large number of rows.
> > > Hope this is understandable
> > > TIA
> > > Ajay