Results 1 to 8 of 8

Thread: VBA Duplicate Value in Column And Perform Calculation

  1. #1
    Neophyte bluesky33's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016

    VBA Duplicate Value in Column And Perform Calculation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    47
    Articles
    0
    Excel Version
    2016 32bit
    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
    Last edited by rollis13; 2021-01-22 at 11:05 PM.

  3. #3
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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/
    Thank you Ken for this secure forum.

  4. #4
    Neophyte bluesky33's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Thank you. I will try it and provide an update.

  5. #5
    Neophyte bluesky33's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    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.

  6. #6
    Neophyte bluesky33's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    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

  7. #7
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    47
    Articles
    0
    Excel Version
    2016 32bit
    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.
    Last edited by rollis13; 2021-01-23 at 09:04 PM.

  8. #8
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @ bluesky
    please wrap your code with code tags ( select the code and press the #button)
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •