duplicate data in columns

[Ajay]

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

 

[Otto Moehrbach]

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

 

[B. R.Ramachandran]

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]

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

 

[Ajay]

Many thanks for this will give it a go
will let you know how successful I am
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

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

 

[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

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

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

 

[B. R.Ramachandran]

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

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

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

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