Data comparison and aggregation - Looking for help!

Dear All,

I am preparing a spreadsheet to do the data analysis, especially analyse and compare the charges between 2 brands. I have individuals, amount, pricing on 2 different brands (charges) and an analysis tabs in the workbook. I have done the price analysis for each individual to see which brand is cheaper.

- I need to do the charges analysis considering the family as a whole, instead of each individual (individuals with same post code).
- individuals tab has individual ID, name and other details.
- Amount tab has individual ID, account number and total value.
- Charges tab has the pricing for 2 brands. analysis tab is where I have done charges analysis for the individuals.
- I need formulas for column O and P in the analysis tab - check the post code for the individuals (column F - individual tab) and if 2 or more individuals have same post code, get their ID (column A). Then, the sum of the total values for each accounts of those particular IDs (from tab amount) to do the charge analysis

Please help.

Thank you.

I could not follow all the logic, but I think I can save you one headache. There is this very convoluted formula on the analysis sheet in column G: a quintuplical nesting of IF statements.

I suggest that you can lookup the percent amount and base amounts using VLOOKUP with the TRUE option. Your data is sorted lowest to highest so the True Option will work. An example of these formulas are on the Charges Sheet.

As for the rest, I think the data should be "normalized" and then a pivot table should do the trick.