Recategorising Data Dynamically

[bcrosby]

Hi,
I have a budget worksheet with multiple worksheets and multiple categories on each worksheet. I have arranged these categories in what I think is a logical manner however, I am a mortgage broker and need to submit these budget spreadsheets to different lenders.
The issue is that each lender categorises the data in a different way.
For example, lender X considers Home Internet to be a household expense whereas Lender Y considers it under Entertainment. For a few categories and a few lenders this could be manageable to simply re-calculate the categories but, I have Excel so why should I be manually recalculating anything. . The other issue is that they use different names for the same thing.
I want some guidance as to the best way to tackle the issue. Should I:

• Use VBA to create an array per lender and have each array contain a list of the lender categories and a list of cells related to that category which are then summed and output? Something like the following pseudo-code:
  • LenderX-Array
    • HouseholdExpenses = sum(F12, F23, F96, F123)
    • ChildhoodExpenses = sum(F9, F50)
    • ...
    • Print (HouseholdExpenses, ChildhoodExpenses, ....)
  • LenderY-Array
    • HomeCosts = sum(F3, F8, F38)
    • ...
    • Print (HomeCosts, ...)
• Create a macro that manipulates the data somehow (I can't picture how at the moment)?

I've thought about putting the lender names/codes against each line item but there are over 30. I don't think adding 30+ columns to contain each lenders' details is going to scale well and it makes the spreadsheet very messy to hand out to clients. I'd rather hide that detail.
I've researched online but everything that I've read so far is based on pivot tables or slices and, without having some categorisation of each cell per lender, as per the previous point, I can't see how they will work.
I'm happy to put in a lot more work before asking more questions but want some guidance as to the best approach to take.
I thank you in advance.