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

duplicate data in columns
 

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" wrote in message
...
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

duplicate data in columns
 

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,Shee t1!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(Shee t1!$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

duplicate data in columns
 

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" wrote in message
...
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

duplicate data in columns
 

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,Shee t1!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(Shee t1!$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

duplicate data in columns
 

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,Shee t1!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(Shee t1!$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

duplicate data in columns
 

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,Shee t1!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(Shee t1!$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