Hard: Need solution to finding a way to check rollup structure

[yoyocancun]

In this photo the left is a predefined structure where all level 4's rollup to their above level 3's, and so on to level one. For example every sunflower is a flower and every flower is a plant... not a tree. Well on the right is a rollup/mapping table where we go as detailed as possible with the levels based on the 'description'. I need to find a way to create a formula or anything (can be ugly) to check the mappings to make sure nothing is unaligned like so highlighted in red; so level 3's have the correct following level 2 etc.. In my document there are 2000+ 'description rows.' thanks in advance!

 

[bebo021999]

K6 = "un-common", next higher lever is "herb". But L6 displays "Plant" (next level of "herb"). Is it accepted a jump like this?

 

[yoyocancun]

K6 = "un-common", next higher lever is "herb". But L6 displays "Plant" (next level of "herb"). Is it accepted a jump like this?

thats an error on my part making the example.

 

[yoyocancun]

Also the structure in my file is 4-500 rows long so it cannot be a manual IF - OR statement

 

[bebo021999]

And what exactly is your outcome for this case?

 

[Peter_SSs]

In future, please consider this for your sample data & expected results:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


Not greatly tested, but this might be a starting point?

yoyocancun.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		plants				plants
4			flowers			flowers|plants
5				sunflower		sunflower|flowers|plants			x	sunflower	flowers	plants
6				rose		rose|flowers|plants			x	thyme	un-common	plants	Check
7			herbs			rose|herbs|plants			x	x	redwoods	trees
8				common		common|herbs|plants			x	x	oak	plants	Check
9					rosemary	rosemary|common|herbs|plants			x	x	birch	trees
10					basil	basil|common|herbs|plants
11				un-common		un-common|herbs|plants
12					thyme	thyme|un-common|herbs|plants
13					cilantro	cilantro|un-common|herbs|plants
14		trees				trees
15			redwoods			redwoods|trees
16			birch			birch|trees
17			oak			oak|trees
Sheet1

Cell Formulas
Range	Formula
M5:M9	M5	=IF(ISNUMBER(MATCH(TEXTJOIN("|",1,IF(I5:L5="x","",I5:L5)),F:F,0)),"","Check")
F3:F17	F3	=LET(tr,LOOKUP("zzz",B$1:B3,ROW(B$1:B3)),TEXTJOIN("|",1,E3,IFERROR(LOOKUP("zzz",INDEX(D$1:D3,tr):D3),""),IFERROR(LOOKUP("zzz",INDEX(C$1:C3,tr):C3),""),INDEX(B$1:B3,tr)))