bluesky33
New member
- Joined
- Jan 22, 2021
- Messages
- 4
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016
[FONT="]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.[/FONT]
[FONT="]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".[/FONT]
[FONT="]Item_code sale_price Difference[/FONT]
[FONT="]123456 $ 123.56 Yes[/FONT]
[FONT="]999999 $ 1,542.00 No[/FONT]
[FONT="]598745 $ 8,455.00 NA[/FONT]
[FONT="]123456 $ 130.99 Yes[/FONT]
[FONT="]546892 $ 562.00 NA[/FONT]
[FONT="]999999 $ 1,542.00 No[/FONT]
[FONT="]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. [/FONT]
[FONT="]Sub compare_dollars_Click()[/FONT]
[FONT="]item_row = Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row[/FONT]
[FONT="]For item_counter = 2 To item_row[/FONT]
[FONT="]get_input = Worksheets("Sheet1").Range("C" & item_counter).Value[/FONT]
[FONT="]If WorksheetFunction.CountIf(Range("C:C"), get_input) > 1 Then[/FONT]
[FONT="]var1 = Range("B" & item_counter)[/FONT]
[FONT="]MsgBox ("Sale Price" & " " & var1 & " " & item_counter)[/FONT]
[FONT="]End If[/FONT]
[FONT="]check_threshold (get_input)[/FONT]
[FONT="]Next item_counter[/FONT]
[FONT="]End Sub[/FONT]
[FONT="]Sub check_threshold(get_input)[/FONT]
[FONT="]Set repeat_cell_address = Worksheets("Sheet1").Range("C:C").Find(get_input, lookat:=xlPart)[/FONT]
[FONT="]newrow = repeat_cell_address.Row[/FONT]
[FONT="]MsgBox (newrow)[/FONT]
[FONT="]var2 = Range("B" & newrow)[/FONT]
[FONT="]MsgBox ("Second occurrence of item code" & " " & var2 & " " & newrow)[/FONT]
[FONT="]End Sub[/FONT]
[FONT="]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".[/FONT]
[FONT="]Item_code sale_price Difference[/FONT]
[FONT="]123456 $ 123.56 Yes[/FONT]
[FONT="]999999 $ 1,542.00 No[/FONT]
[FONT="]598745 $ 8,455.00 NA[/FONT]
[FONT="]123456 $ 130.99 Yes[/FONT]
[FONT="]546892 $ 562.00 NA[/FONT]
[FONT="]999999 $ 1,542.00 No[/FONT]
[FONT="]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. [/FONT]
[FONT="]Sub compare_dollars_Click()[/FONT]
[FONT="]item_row = Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row[/FONT]
[FONT="]For item_counter = 2 To item_row[/FONT]
[FONT="]get_input = Worksheets("Sheet1").Range("C" & item_counter).Value[/FONT]
[FONT="]If WorksheetFunction.CountIf(Range("C:C"), get_input) > 1 Then[/FONT]
[FONT="]var1 = Range("B" & item_counter)[/FONT]
[FONT="]MsgBox ("Sale Price" & " " & var1 & " " & item_counter)[/FONT]
[FONT="]End If[/FONT]
[FONT="]check_threshold (get_input)[/FONT]
[FONT="]Next item_counter[/FONT]
[FONT="]End Sub[/FONT]
[FONT="]Sub check_threshold(get_input)[/FONT]
[FONT="]Set repeat_cell_address = Worksheets("Sheet1").Range("C:C").Find(get_input, lookat:=xlPart)[/FONT]
[FONT="]newrow = repeat_cell_address.Row[/FONT]
[FONT="]MsgBox (newrow)[/FONT]
[FONT="]var2 = Range("B" & newrow)[/FONT]
[FONT="]MsgBox ("Second occurrence of item code" & " " & var2 & " " & newrow)[/FONT]
[FONT="]End Sub[/FONT]