VBA Duplicate Value in Column And Perform Calculation

[bluesky33]

I have two columns in my spreadsheet. If the value in column A repeats, then I want to get the corresponding value in column B for both these values and compare them. If there is a difference greater than say $10, write Yes in column C.

Example, code 123456 appears twice in column A, and there is a difference of greater than $10 so I have to write "Yes" in column C. On the other hand, 999999 appears twice but there is no difference so I have to write "No".

Item_code sale_price Difference
123456 $ 123.56 Yes
999999 $ 1,542.00 No
598745 $ 8,455.00 NA
123456 $ 130.99 Yes
546892 $ 562.00 NA
999999 $ 1,542.00 No

Giving below the code, not sure why it is not identifying the next occurrence of the same value and getting the relative value from column B. It just keeps getting me the first occurrence. Any help is very much appreciated.

Sub compare_dollars_Click()

item_row = Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

For item_counter = 2 To item_row

get_input = Worksheets("Sheet1").Range("C" & item_counter).Value

If WorksheetFunction.CountIf(Range("C:C"), get_input) > 1 Then

var1 = Range("B" & item_counter)
MsgBox ("Sale Price" & " " & var1 & " " & item_counter)
End If

check_threshold (get_input)

Next item_counter

End Sub


Sub check_threshold(get_input)

Set repeat_cell_address = Worksheets("Sheet1").Range("C:C").Find(get_input, lookat:=xlPart)

newrow = repeat_cell_address.Row

MsgBox (newrow)
var2 = Range("B" & newrow)
MsgBox ("Second occurrence of item code" & " " & var2 & " " & newrow)

End Sub

 

[bluesky33]

You're welcome & thanks for the feedback.
If you want to ignore the difference of 10, it's a simple fix, just let me know.

Thank you for the offer. Very kind of you.

I have to check a few conditions. I will give it a try while I learn your solution. If I can't figure it out, will reach out again.

 

[Fluff]

Ok,