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!
[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!