Duplicates

[Soondaram]

actually I have got 5 columns each one next to the other
for example :
A B C D E

row1 2 3 4 2 3
row2 1 5 11 5 1
row3 2 4 6 8 10

what I want is a formula that looks for duplicates from cellA1 to cellE1 and
if possible remove them by adjusting the values
that is I should get the following as result:

A B C D E

row1 2 3 4 0 0
row2 1 5 11 0 0
row3 2 4 6 8 10

OR simply returns me the following

A B C D E F

row1 2 3 4 2 3 Duplicates
row2 1 5 11 5 1 Duplicates
row3 2 4 6 8 10 NoDuplicates


hope to get a solution to this problem.

Thanking you
Soondaram.

 

[Nikos Yannacopoulos]

Soondaram,

I don't think the first one could be done through formulae, I believe it
requires some VB code.
The second one can be done with simple worksheet functions (although, again,
a VB function would be more efficient) as follows:

=IF(COUNTIF(A1:E1,A1)+COUNTIF(A1:E1,B1)+COUNTIF(A1:E1,C1)+COUNTIF(A1:E1,D1)+
COUNTIF(A1:E1,E1)>5,"Duplicates","No Duplicates")

assuming the actual data starts in A1 and the formula is entered in F1; then
just copy down.

HTH,
Nikos

 

[RagDyeR]

You could try this array formula, slightly shorter:

=IF(MAX(COUNTIF(A1:E1,A1:E1))>1,"Duplicates","No Duplicates")

Since it's an array formula, it must be entered with CSE (<Ctrl> <Shift>
<Enter>)
*NOT* just with <Enter>.
If done correctly, the formula will *automatically* be enclosed in curly
brackets.

Then, just drag down to copy.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Soondaram,

I don't think the first one could be done through formulae, I believe it
requires some VB code.
The second one can be done with simple worksheet functions (although, again,
a VB function would be more efficient) as follows:

=IF(COUNTIF(A1:E1,A1)+COUNTIF(A1:E1,B1)+COUNTIF(A1:E1,C1)+COUNTIF(A1:E1,D1)+
COUNTIF(A1:E1,E1)>5,"Duplicates","No Duplicates")

assuming the actual data starts in A1 and the formula is entered in F1; then
just copy down.

HTH,
Nikos