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
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