Finding Data from Other Columns

jonwhite1234

New Member
Joined
Jan 5, 2019
Messages
2
Good morning gang. Hope you're all well. My question is around a worksheet I have like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Division[/TD]
[TD]Business Unit[/TD]
[TD]ID[/TD]
[TD]Owner[/TD]
[TD]Value Steam[/TD]
[TD]Tier[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Championship[/TD]
[TD]Delivery 1[/TD]
[TD]14[/TD]
[TD]John Smith[/TD]
[TD]Stream 4[/TD]
[TD]Tier B[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Championship[/TD]
[TD]Delivery 1[/TD]
[TD]14[/TD]
[TD]John Smith[/TD]
[TD]Stream 2[/TD]
[TD]Tier A[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Premier[/TD]
[TD]Delivery 3[/TD]
[TD]15[/TD]
[TD]Fred Bloggs[/TD]
[TD]Stream 7[/TD]
[TD][/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Premier[/TD]
[TD]Delivery 3[/TD]
[TD]15[/TD]
[TD]Craig Smith[/TD]
[TD]Stream 8[/TD]
[TD]Tier C[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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! :)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Re: Finding Data from Other Columns help!

Hello,

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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
Back
Top