Formula to locate duplicate products with the lowest price

Twinkievizzio11

New Member
Joined
Apr 27, 2017
Messages
28
Example Data:

Column B Column C Column F
Apple 1234 1.00
Orange 1234 1.25
Orange 4321 5.00
Pineapple 4321 3.50
Banana 4321 3.50
Grape 5678 1.00
Orange 5678 1.00
Pineapple 5678 1.00
Apple 5678 1.00
Berry 5678 1.50
Grape 5678 1.00
Orange 6666 1.00
Pineapple 6666 1.00
Grape 6666 1.00
Berry 6666 1.50


Output:

If Column C is the same number ID then give me the lowest price in Column F
If the lowest price in Column F and the Number ID in Column C are equal then give me the row that has the Name "Apple". If "Apple" is not there then give me the row that has the name "Grape" if Grape is not there then just give me the row that has the lowest price for that Number ID.


Results should look like:

Apple 1234 1.00
Pineapple 4321 3.50
Apple 5678 1.00
Grape 6666 1.00


If anyone can help Id truly appreciate it.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Re: Help with formula to locate duplicate products with the lowest price

How about, this will output the data in Cols A:C on a sheet called Summary
Code:
Sub GetLowestWithTwist()

   Dim Rng As Range
   Dim Cl As Range
   Dim itm As Variant
   Dim NxtRw As Long
   
   With Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
      Set Rng = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
   End With
   NxtRw = Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("B" & Rows.Count).End(xlUp).Offset(1).row
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Rng
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Array(Cl.Offset(, 3).Value, Cl.Offset(, -1).Value)
         ElseIf .Item(Cl.Value)(0) > Cl.Offset(, 3).Value Then
            .Item(Cl.Value) = Array(Cl.Offset(, 3).Value, Cl.Offset(, -1).Value)
         ElseIf .Item(Cl.Value)(0) = Cl.Offset(, 3).Value Then
            Select Case Cl.Offset(, -1).Value
               Case "[COLOR=#0000ff]Apple[/COLOR]"
                  .Item(Cl.Value) = Array(Cl.Offset(, 3).Value, Cl.Offset(, -1).Value)
               Case "[COLOR=#0000ff]Grape[/COLOR]"
                  If Not .Item(Cl.Value)(1) = "Apple" Then .Item(Cl.Value) = Array(Cl.Offset(, 3).Value, Cl.Offset(, -1).Value)
            End Select
         End If
      Next Cl
      
      Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("B" & NxtRw).Resize(.Count).Value = Application.Transpose(.keys)
      For Each itm In .items
         Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("A" & NxtRw).Value = itm(1)
         Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("C" & NxtRw).Value = itm(0)
         NxtRw = NxtRw + 1
      Next itm
   End With
   
End Sub
Change sheet names in red to suit, along with values in blue.
 

Twinkievizzio11

New Member
Joined
Apr 27, 2017
Messages
28
Re: Help with formula to locate duplicate products with the lowest price

Hi there,

Thank you however this is not working :( I am receiving an error "subscript out of range". I changed the values in Blue and red to suit my data but still does not work. Any suggestions to fix it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Re: Help with formula to locate duplicate products with the lowest price

What line gave the error?
 

Twinkievizzio11

New Member
Joined
Apr 27, 2017
Messages
28

ADVERTISEMENT

Re: Help with formula to locate duplicate products with the lowest price

I went to the module and selected F8 I am received a debug on line

NxtRw = Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1).Row


Not sure if that is how you find which line had the error

Also the data has a header in Row 1.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Re: Help with formula to locate duplicate products with the lowest price

Check that you have the correct sheet name, especially lookout for any leading/trailing spaces
 

Twinkievizzio11

New Member
Joined
Apr 27, 2017
Messages
28
Re: Help with formula to locate duplicate products with the lowest price

I added a "Summary" tab and now it worked!!

Thank you so much for all of your help. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Re: Help with formula to locate duplicate products with the lowest price

Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,192
Members
414,513
Latest member
junbuggle

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