VBA Duplicate Value in Column And Perform Calculation

bluesky33

New Member
Joined
Jan 22, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
You maybe can use the parameter AFTER bij FIND.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Can the Item code occur more that twice? If so what should happen?
 

bluesky33

New Member
Joined
Jan 22, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

bluesky33

New Member
Joined
Jan 22, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

bluesky33

New Member
Joined
Jan 22, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top