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

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Duplicate Value in Column And Perform Calculation
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[bluesky33]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Duplicate Value in Column And Perform Calculation
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks, I had posted it on excel guru as well. Here's the link.

VBA Duplicate Value in Column And Perform Calculation

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

www.excelguru.ca

 

[Fluff]

Thanks for the link, did the code I suggested work?

 

[bluesky33]

Thanks for the link, did the code I suggested work?

Sorry, it did not work in full. I have given below the results from the test I just ran.

1. item_code 999 has no difference in sale_price so this worked. Is it possible to have the "No Difference" message on both the occurrence of item_code 999?

2. item_code 1982 has a difference in sale_price so this should have said something like "Difference exists".

3. For other codes like 1000, 334 and 28176, since they exist only once, can we just have a text like "NA" in column C?

 

[bluesky33]

Thanks for the link, did the code I suggested work?

Unfortunately, it did not work. I just posted the test response there as well.

 

[Fluff]

Ok, how about

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
            Cl.Offset(, 2).Value = "NA"
         Else
            If Abs(Cl.Offset(, 1).Value - .Item(Cl.Value).Offset(, 1).Value) > 10 Then
               Union(.Item(Cl.Value).Offset(, 2), Cl.Offset(, 2)) = "Difference Exists"
            Else
               Union(.Item(Cl.Value).Offset(, 2), Cl.Offset(, 2)) = "No Difference"
            End If
         End If
      Next Cl
   End With
End Sub

 

[bluesky33]

Ok, how about

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
            Cl.Offset(, 2).Value = "NA"
         Else
            If Abs(Cl.Offset(, 1).Value - .Item(Cl.Value).Offset(, 1).Value) > 10 Then
               Union(.Item(Cl.Value).Offset(, 2), Cl.Offset(, 2)) = "Difference Exists"
            Else
               Union(.Item(Cl.Value).Offset(, 2), Cl.Offset(, 2)) = "No Difference"
            End If
         End If
      Next Cl
   End With
End Sub

Just one last problem. Code 1982 should have a "Difference Exists" since the sale_price is different. Please see attached test result.

 

[Fluff]

So you no longer want to check for

a difference of greater than $10

 

[bluesky33]

So you no longer want to check for

Oops!! My bad.

Thanks a ton for your help and time. The code works great. Have a great weekend.

 

[Fluff]

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.