Formula to locate duplicate products with the lowest price

[Twinkievizzio11]

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.

 

[Fluff]

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

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]

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]

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

What line gave the error?

 

[Twinkievizzio11]

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.

 

[Fluff]

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]

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]

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

Glad to help & thanks for the feedback