#1
|
|||
|
|||
Formula Help
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 |
#2
|
|||
|
|||
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$11,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 |
#3
|
|||
|
|||
Many thanks I will give this a go tomorrow.
Thanks again. |
Thread Tools | |
Display Modes | |
|
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 |