VBA Duplicate Value in Column And Perform Calculation

**bluesky33** · 2021-01-22, 03:25 PM

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

**rollis13** · 2021-01-22, 11:58 PM

Try with your revisited code, maybe it's closer to your needs.

Code:

Option Explicit                                   '<=added
Dim item_counter                                  '<=added

Sub compare_dollars_Click()

    Dim item_row, get_input, var1                 '<=added

    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 & " at row " & item_counter)
        End If
        check_threshold (get_input)
    Next item_counter
    
End Sub

Code:

Sub check_threshold(get_input)

    Dim repeat_cell_address, newrow, var2         '<=added

    Set repeat_cell_address = Worksheets("Sheet1").Range("C:C").Find(get_input, _
         after:=Worksheets("Sheet1").Range("C" & item_counter), lookat:=xlPart) '<=changed
    newrow = repeat_cell_address.Row
    MsgBox (newrow)
    var2 = Range("B" & newrow)
    MsgBox ("Second occurrence of item code" & " " & var2 & " at row " & newrow)
    
End Sub

**Pecoflyer** · 2021-01-23, 02:47 PM

Hi and welcome
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question
As you are new here I will add it for you this once

https://www.mrexcel.com/board/thread...ation.1159276/

**bluesky33** · 2021-01-23, 05:38 PM

Thank you. I will try it and provide an update.

**bluesky33** · 2021-01-23, 06:40 PM

Hi, I tried the code and it is generating random item codes like 66, 2, 1 etc which are not in the item code list. It did however work for item code 999 and bring up the correct sale_price. Other numbers were arbitrary. Thanks.

**bluesky33** · 2021-01-23, 07:26 PM

Hi, I got a solution from mrexcel. Thank you very much for your help.

Here's the link to the post if you are interested. It has some added functionality.

https://www.mrexcel.com/board/thread...6/post-5625150

**rollis13** · 2021-01-23, 09:57 PM

Probably your real data has nothing to do with what you listed in post #1; I don't have a crystal ball.
Maybe changing the line of code:

Code:

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

to this would make it work differently:

Code:

Set repeat_cell_address = Worksheets("Sheet1").Range("C:C").Find(get_input, _
         after:=Worksheets("Sheet1").Range("C" & item_counter), lookat:=xlWhole)

but I'm almost sure I didn't understand your request.