Complex Custom User Form

I know this is a lot to ask so thank you very much if you even attempt/have attempted to help me with this.

I have a complex problem that I could use some help with. It is extremely long and difficult so if you have the time and generosity to help me I will be much In your debt.

I am designing a financial model which is capable of mapping a clients chart of accounts/trial balance (codes that describe each line item in a financial statement) to my companies chart of accounts (this is necessary because none of these clients use the uniform system of accounts). The mapping then uploads the clients data into a standardized financial statement (using sumifs() in order to eliminate unnecessary line item detail) which then acts as an input for the financial models tools.

This mapping system works via a 5 selections criteria that allow a user to identify a clients line item and concatenate(vlookup1,vlookup2,...,vlookupN) to create a trial balance (identifier code) that then matched with the clients line item trial balance code, and allows the information to be pulled into the correct line item in our financial statement (input sheet). In case this is unclear I have attached an example of this mapping procedure simplified that I found online. Copy of MappingTables.xlsx

Currently, the input sheet is fixed and massive (over 2500 rows) and allows for little customization. Does anyone have an idea to make this more dynamic and generate only the line items that are defined in the mapping system so that the user doesn't have to deal with such a massive form when not necessary.

To clarify. If the client's sheet has a line item, "Food and beverage expense" and our system of accounts calls that "F&B expense" I would map this using the descriptors (one in each column next to the line item). "Food&Beverage","Restaurant1","Expense","None","F&B Expense" which would generate the trial balance code: 120-100-150-000-5410 (the reason for such a complex descriptor system is because many departments could have the same line item (last code). In addition, this allows us to do subtotaling between departments by any of the descriptors, IE outlet type (restaurant1), metric type (expense), ect...).

Based on the above, is there a way that excel could read through the descriptor codes (or descriptor names themselves) and assemble the input sheet so that it contained: (1) A header labeled Food and Beverage, (2) a sub header labeled: "Restaurant1",a line item name(F&B expense), and (4) upload the correct account balance (say $50) next to the correct line item (as it already does). All this while keeping a good financial statement format (see below) and including subtotals.

Finally, if possible, it also needs to allow users to create custom line items and keep the account numbers associated with these line items uniform across several excel worksheets. So multiple users do not create the same custom line item under different numbers (ie. one creates "F&B expense" with line item number 5410 and another create it with the name F&B Expense and give it number 5420.

Good financial system format:

In General Form:

Department
SubDepartment
Metric Header ( in order of Revenue, Labor and Benefits Expense, Operating Expense)
Line Item Name
Totals and subtotals

In Non General Form:

Food and Beverage
Restaurant1
Revenue
Sale of Food
Breakfast Revenue
Lunch Revenue
Dinner Revenue
Subtotal
Sale of Beverage
Breakfast Revenue
Lunch Revenue
Dinner Revenue
Subtotal
Total Revenue Subtotal
Covers*
Sale of Food
Breakfast Covers
Lunch Covers
Dinner Covers
Subtotal
Sale of Beverage
Breakfast Covers
Lunch Covers
Dinner Covers
Subtotal
Total Covers Subtotal
Labor and Benefits Expense
Labor
Benefits
Subtotal
Operating Expenses
Silverware
Napkins
Subtotal
Total Expenses Subtotal

Profit (Total Revenue Subtotal-Total Expenses Subtotal)

*Incase you don't know, a cover is a single customer



I know this is a lot to ask so thank you very much if you even attempt/have attempted to help me with this.