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

 

[mart37]

You maybe can use the parameter AFTER bij FIND.

 

[Fluff]

Hi & welcome to MrExcel.
Can the Item code occur more that twice? If so what should happen?

 

[bluesky33]

Hi & welcome to MrExcel.
Can the Item code occur more that twice? If so what should happen?

Hi, thank you.

Yes, they could occur more than twice. One key point I forgot to mention in the post is that I have to compare the values from two systems. So if there are 4 occurrences of a item code from the first system, there will be 4 occurrences from the second system too.

 

[Fluff]

So do you want to check the maximum difference between the 4 instances of the item?

 

[bluesky33]

So do you want to check the maximum difference between the 4 instances of the item?

No, if I can compare the differences for two values for now, that will help. Sorry for the confusion I'm causing.

Comparing the amounts for two occurrences of the item code for now is good.

 

[Fluff]

If you have more than two of the same item, which two values do you want to compare?

 

[bluesky33]

If you have more than two of the same item, which two values do you want to compare?

We will have have only two of the same item codes. In my example, 123456 occurs only twice. So, the sale_price of the first occurrence and the second occurrence has to be compared. If they are greater than $10, then write a comment in column C. Thank you.

 

[Fluff]

Then why did you say?

Yes, they could occur more than twice.

 

[Fluff]

Try

Sub bluesky()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl
         Else
            If Abs(Cl.Offset(, 1).Value - .Item(Cl.Value).Offset(, 1).Value) > 10 Then
               Cl.Offset(, 2) = "Yes"
            Else
               Cl.Offset(, 2) = "No"
            End If
         End If
      Next Cl
      Next Cl
   End With
End Sub