Hi



I am looking for some help with a formula for searching for duplicate entries in two adjacent columns and then adding "0.3" to each of the duplicate column values.
I have Data as follows:
Column D Column E
1 1
3 2
2 2
1 1

I want the duplicate row to become:
1.3 1.3


Any help would be appreciated.

Thanks in advance.

Tony

On fly, let's assume your datarange is D2:E10

Into F2 enter the formula
=$D2 + AND(COUNTIFS($D$2:$D2,$D2)>1,COUNTIFS($E$2:$E2,$E2 )>1)*0,3
Into G2 enter the formula
=$E2 + AND(COUNTIFS($D$2:$D2,$D2)>1,COUNTIFS($E$2:$E2,$E2 )>1)*0,3

Copy F2:G2 down to F10:G10

Hi
Many thanks for the quick response but this does not appear to work.
Kind regards
Tony

Please see attached sample worksheet detailing the challenge. This may need a VBA solution but I am trying to avoid that approach if I can.
Tony

Attached Files

Bubble Chart Challenge.xlsx (9.5 KB, 14 views)

How is row 9 in your example a duplicate of row 8?

I think you could use:

=C2+COUNTIFS($C$1:$C1,$C2,$D$1:$D1,$D2)*0.3

Hi
That appears to work brilliantly.
Many thanks for your help.
Tony

The formulas work!

In returned workbook, into columns J:K new values for both columns are calculated. In cell N1 FALSE indicates, that there are differences with values in original table - so copy values from J2:K17 into D2:E17 (Use PasteSpecial>Values to copy only values, not formulas!)

When after that in N1 the formula returns TRUE, then there aren't any duplicate rows in D2:E17 anymore, otherwise repeat coping values from J2:K17 into D2:E17.

Attached Files

BubbleChartChallenge.xlsx (10.3 KB, 19 views)

AND(COUNTIFS($D$2:$D2,$D2)>1,COUNTIFS($E$2:$E2,$E2 )>1)
doesn't actually determine that the duplication happened on the same row for both columns.