2 colums of data, trying to specify cheapest one of groups by matching names

Hello,

I'am trying to see if it is possble to, with the attached data, that is sorted by "variant name" (column A) asside from the spaces, look through column A to find groups of "variant names" that are the same, then compare the prices (column c) of these rows in the "variant name" groups and in column B enter yes for the cheapest one of the "variant name" group and no for the rest.

Considering that in some "variant name" groups the prices may all be the same and then it would not matter which one was selected the first one down the list or last is as good as any if thats easiest or also there may be several different prices for items in the "variant name" groups but there may be more than one of them that are the cheapest price, again it would not matter which one was made to be yes in colum B of these matching cheapest prices with in a "variant name" group but - only 1 of any "variant name" group can be yes the rest have to be no.

This is a bit complicated, but I hope it makes sense. Hopefully this is possible, but I dont have the excel skills to be able to knock up the code required to do this.

I hope someone can help.

Best regards,

Paul

This is my interpretation of your problem.

To expand on the post from Max, you don't want to have any Yes or No on rows where Variant Title is blank. With the array
formula in D2, the formula in B2 to display either "Yes" or "No" would be:

=IF(TRIM(A2)="","",IF(C2=D2,"Yes","No"))

I think Max may have intended to type "A2" instead of "A7" in his array formula example.
Jerry

Hello,

Thank you very much for looking at this problem for me.

Max and xljer - This works very well though I get "yes" coming up for all rows in a group that are the cheapest rather than just one of them even if both of them are the cheapest. or all of them are the same price.

Jindon - your button version works almost perfectly, where the variant title colum entries are blank it would be good to have nothing entered in the column B, though this is not a major problem. I need to be able to transfer this marco or formula to a much larger spreadsheet of data, is there anyway this button version could be made like a module that could be called into action by entering a function into a column? or as a key stroke?

Thank you everyone for your help so far this is very helpful.

Paul

Key : Ctrl + BackSpace

To ThisWorkbook module

Private Sub Workbook_Activate()
    Application.OnKey "^{bs}", "test"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "^{bs}", ""
End Sub

To a Standard Module

Option Explicit

Sub test()
    Dim a, b, i As Long, n As Long, flg As Boolean, e, w
    With Sheets("Sheet1")
        a = .Range("a1", .Cells.SpecialCells(11)).Value
        ReDim b(1 To UBound(a, 1), 1 To 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then
                        .Item(a(i, 1)) = VBA.Array(a(i, 3), i)
                        b(i, 1) = "yes"
                    Else
                        w = .Item(a(i, 1))
                        If w(0) > a(i, 3) Then
                            b(i, 1) = "yes"
                            b(w(1), 1) = "no"
                            w(0) = a(i, 3)
                            w(1) = i
                            .Item(a(i, 1)) = w
                        Else
                            b(i, 1) = "no"
                        End If
                    End If
                End If
            Next
        End With
        .Range("b1").Resize(UBound(b, 1)).Value = b
    End With
End Sub

Hi jindon,

This is perfect, thanks very much for this exactly what I needed.

Thanks once again.

Paul

That's good and thanks for the feed back.

Hi Jindon,

I'm just trying to impliment the code you created on my main spreadsheet and I'm trying to change the code to tell it where the relevant data is and the a, b, c, columns of the spreadsheet I uploaded with example data I need to change to v, ab, ao, respectively. I think I can see where to change the a and b column references but cant see where the c colum is referenced from the code, could you help with that at all? I've made the below edit, but it doesnt work due to my above problem, hope you can help.

Option Explicit

Sub test()
    Dim a, b, i As Long, n As Long, flg As Boolean, e, w
    With Sheets("Sheet1")
        a = .Range("v1", .Cells.SpecialCells(11)).Value
        ReDim b(1 To UBound(a, 1), 1 To 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then
                        .Item(a(i, 1)) = VBA.Array(a(i, 3), i)
                        b(i, 1) = "yes"
                    Else
                        w = .Item(a(i, 1))
                        If w(0) > a(i, 3) Then
                            b(i, 1) = "yes"
                            b(w(1), 1) = "no"
                            w(0) = a(i, 3)
                            w(1) = i
                            .Item(a(i, 1)) = w
                        Else
                            b(i, 1) = "no"
                        End If
                    End If
                End If
            Next
        End With
        .Range("ab1").Resize(UBound(b, 1)).Value = b
    End With
End Sub

Hope you can help and look forward to hearing from you.

Thanks and best regards,

Paul

Try replace all a(i, 3) with a(i, 15)

Thanks again Jindon.

All working fine perfectly now.

Cheers,

Paul

Hi,

I'm having some problems trying to use the above module in a different spread sheet. I have the below where column Y has the titles in, column AN is where I want the yes or no answers and column BD is the column with the prices that need comparing.

Sub test()
    Dim a, b, i As Long, n As Long, flg As Boolean, e, w
    With Sheets("Parker Range")
        a = .Range("y1", .Cells.SpecialCells(11)).Value
        ReDim b(1 To UBound(a, 1), 1 To 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then
                        .Item(a(i, 1)) = VBA.Array(a(i, 16), i)
                        b(i, 1) = "yes"
                    Else
                        w = .Item(a(i, 1))
                        If w(0) > a(i, 16) Then
                            b(i, 1) = "yes"
                            b(w(1), 1) = "no"
                            w(0) = a(i, 16)
                            w(1) = i
                            .Item(a(i, 1)) = w
                        Else
                            b(i, 1) = "no"
                        End If
                    End If
                End If
            Next
        End With
        .Range("an1").Resize(UBound(b, 1)).Value = b
    End With
End Sub

The spread sheet i'm trying to apply this too has over 5200 rows, when I apply this it makes the calculation and inserts Yes's and No's though it either puts them as the first of a group(in Y column) or the (second row in a group) and this changes every time I re-do the calculation even if I make no changes to the workbook module.

Any help with this would be gratefully received.

Cheers.

Change to a(i, 32)

Hi jindon,

That sorted it out thanks very much.