duplicate data in columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

B. R.Ramachandran said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top