Alternative to manual mapping ?
There were only 100+ rows in example database sheet. Manually adding macro categories(aka mapping) was easy for such smaller data set.
However, real world might have much bigger data sets, how should we do mapping there ?
It would have been good to see automatic mapping instead of manually doing it. Is there a way to do it ?
You can't really do automatic mapping if you haven't done it at least once before.
If you have done this exercise for a previous year, then you can do a VLOOKUP (or INDEX&MATCH) to transfer some of the corresponding values.
This can't be done automatically the first time though.
I use =si.conjunto, which I think in english is =ifs, for example:
=SI.CONJUNTO(C5="Core business revenues","Net Sales",C5="Other revenues","Other revenues") etc.
It can be made even more automatic if instead of typing C5="Core business revenues", you place all the P&L Accounts in an array of cells as placeholders, and when typing the formula you reference those placeholder cells, so you would be typing C5=$A$1 (supposing A1 has "Core business revenues" as its text value).