Finding Data from Other Columns

[jonwhite1234]

Good morning gang. Hope you're all well. My question is around a worksheet I have like this:

Division	Business Unit	ID	Owner	Value Steam	Tier	Status
Championship	Delivery 1	14	John Smith	Stream 4	Tier B	Good
Championship	Delivery 1	14	John Smith	Stream 2	Tier A	Good
Premier	Delivery 3	15	Fred Bloggs	Stream 7		Bad
Premier	Delivery 3	15	Craig Smith	Stream 8	Tier C	Good

<tbody>
</tbody>

There are around 45000 rows - it's a beast. Tier options are Tier A, Tier B, Tier C or blank. What I what to do is to search on each ID number and where there is a Tier rating work out how high it is and then duplicate that rating across all rows for that ID number. So in the example above the code or formula would put "Tier A" in the Tier column for all rows with ID 14 and "Tier C" for all rows with ID 15.

Many moons ago I think I used and Index and Match with an MIN IF Statement to do something similar but I can't for the life of me make it work. Do you know if this is easy to code in VBA or if there's a formula I can use on another column for example to work it out then overwrite?

Any help at all would be just great!

 

[onlyadrafter]

Re: Finding Data from Other Columns help!

Hello,

This works for the data supplied. Have assumed columns N and O are avaailable

Sub TIER_2()
    Application.ScreenUpdating = False
    Columns("C:C").Copy Range("N1")
    Range("N1:N" & Range("N" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    For MY_ID_ROW = 2 To Range("O" & Rows.Count).End(xlUp).Row
        MY_ID = Range("O" & MY_ID_ROW).Value
        Range("A1:G" & Range("D" & Rows.Count).End(xlUp).Row).AutoFilter Field:=3, Criteria1:=MY_ID
            Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
            For Each CELL In Selection
                MY_TIER = CELL
                If MY_TIER = "Tier A" Then
                    MY_TIER_VALUE = "Tier A"
                    GoTo CHANGE_TIER
                End If
                If MY_TIER = "Tier B" Then
                    If MY_TIER_VALUE = "" Or MY_TIER_VALUE = "Tier C" Then
                        MY_TIER_VALUE = MY_TIER
                    End If
                End If
                If MY_TIER = "Tier C" Then
                    If MY_TIER_VALUE = "" Then
                        MY_TIER_VALUE = MY_TIER
                    End If
                End If
                
        Next CELL
CHANGE_TIER:
            Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
            For Each CELL In Selection
                CELL.Value = MY_TIER_VALUE
            Next CELL
            MY_TIER_VALUE = ""
        Selection.AutoFilter
    Next MY_ID_ROW
    Columns("N:O").ClearContents
    Application.ScreenUpdating = True
End Sub

it is a bit convoluted, but its been quite a long day.

 

[jonwhite1234]

Re: Finding Data from Other Columns help!

Thanks so much, that is absolutely brilliant!!