Help Ranking Dynamic Text, Ranking Lists Within A Separate Ranked List

[scoobysteph]

Hello everyone,
I'm trying to create ranked subset lists from a 'master' ranked list and have subset lists update dynamically as changes are made to the master list. Boiling it down to a very simple example below:

What I have (sample master list) of overall rankings:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Vegetable[/TD]
[TD]Carrot[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Vegetable[/TD]
[TD]Onion[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Junk Food[/TD]
[TD]Chips[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Junk Food[/TD]
[TD]Popcorn[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Fruit[/TD]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Fruit[/TD]
[TD]Lime[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Vegetable[/TD]
[TD]Potato[/TD]
[/TR]
</tbody>[/TABLE]

What I want is to have separate rankings by subset that is updated as changes are made to the master ranking list:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD]Junk Food[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Carrot[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Chips[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Onion[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Lemon[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Popcorn[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Potato[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Lime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hopefully there's a way to do this with formulas. I'm getting confused as to where to start because most of it is based on text inputs. I'm not familiar with VBA so I hope there's a way to use count/match/index for this....

THANK YOU!

 

[sheetspread]

This post has formula and VBA solutions

https://www.mrexcel.com/forum/excel-questions/683122-column-listing-items-table-data.html