For Question 1. , I make instruction in Sheet formula. Please check attached file
1. Arrange column according to Occurrence sheet, select Column D > Ctrl+X > right click at column C > insert Cut cells
2. sort by press Alt A S S then add level as in the Pic 2.
3. select column F > Press Ctrl + + to insert 2 columns
4. select column B:C > Press Alt A Q > set as image below
5. Select Sheet Occurrence E2:E4, Alt A V V > list > enter formula
Formula:
=OFFSET(Formulas!$G$1,MATCH($D2,Formulas!$F$2:$F$20,0),,COUNTIF(Formulas!$F$2:$F$20,$D2))
6. Select Sheet Occurrence F2:F4, Alt A V V > list > enter formula
Formula:
=OFFSET(Formulas!$D$1,MATCH(D2&E2,Formulas!$B$2:$B$135&Formulas!$C$2:$C$135,0),,SUMPRODUCT(--(D2&E2=Formulas!$B$2:$B$135&Formulas!$C$2:$C$135)))
Bookmarks