How to pigeon-hole new data in correct column?

Hi excel experts,
since years I kept visiting this awesome community and found many problems solved!
But the following problem kept me thinking for 1 year now and I did not find anything in the search

How can I pigeon-hole the new data on the right in the correct column? (see attachment)
Since I have many and huge worksheets, I really need an automated way to do this now...manual copy pasting does not do the trick anymore.
Simply sorting does not work, since some worksheets are missing variables and others have additional ones (which I need also).

Thank you for any hint or idea.
Flow

PS: I found this thread though, but it does not seem to do the trick for me...or I do not fully grasp it: excelforum.com/excel-formulas-and-functions/667706-pigeon-holing-rows-of-information.html

Are your colours just there to help explain your requirements, or do you want the same colours to appear in Sheet3 ?

A formula cannot generate those colours in the composite sheet.

Pete

Thank you for the fast reply. Correct, the colors are just there to help visualizing the problem.

(but if I cut&paste the cells manually the colors go with it. Essentially this is also a sorting & cut&paste task.
I thought about semi-automating this the following :

Please Login or Register  to view this content.

But maybe this is the wrong approach that blocks me from solving this.

In my first picture I also forgot a further thing making this more complicated:
Depending on the year the variables have more&less rows

An option:

Please add the missing headings manually and try this formula:

Formula:

Please Login or Register  to view this content.