Formula Help

OTPM · #1 10-18-2017, 06:02 AM

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

Roger Govier · #2 10-22-2017, 06:36 AM

Hi

You can't do it in the same cell by formula, only with VBA.
For a formula solution, in cell G1 enter
=D1+(COUNTIF($D$1

1,D1)-1)*0.3 and copy down
and in H1
=E1+(COUNTIF($E$1:E1,E1)-1)*0.3

If you want to do it in the cells, then this code will change the values in columns D and E

Code:

Sub Add3()

    Dim lr As Long, i As Long, x As Long
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    For i = 1 To lr
        x = Application.CountIf(Range(Cells(1, "D"), Cells(i, "D")), Cells(i, "D"))
        If x > 1 Then
            Cells(i, "D") = Cells(i, "D") + (x - 1) * 0.3
        End If
        x = Application.CountIf(Range(Cells(1, "E"), Cells(i, "E")), Cells(i, "E"))
        If x > 1 Then
            Cells(i, "E") = Cells(i, "E") + (x - 1) * 0.3
        End If
    Next i

End Sub

OTPM · #3 10-22-2017, 06:38 AM

Many thanks I will give this a go tomorrow.
Thanks again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula	innkeeper9	Excel	2	09-13-2016 08:59 PM
if(and.. formula	paxon	Excel	8	05-19-2016 08:05 AM
IIf Formula	jkdiesel	Project	3	05-17-2016 01:01 AM
Need help with dragging a formula and changing a reference column as I drag the formula.	LupeB	Excel	1	10-22-2015 03:02 PM
Help with formula please.	AndrewSE	Excel	3	04-05-2011 08:50 PM