Excel Dynamic Grouping/Hierarchy Tool

Question

Let's say I have a dataset that looks like this:

Raw Data (for Excel):

HouseholdID HouseholdMemberID   Name    AnnualIncome
1   1.1 Man $50,000 
1   1.2 Woman   $50,000 
1   1.3 Boy $2,000 
1   1.4 Girl    $50 
2   2.1 Man $0 
2   2.2 Woman   $125,000 
2   2.3 Girl    $0 
3   3.1 Man $45,000 
3   3.2 Girl    $20 
4   4.1 Man $80,000 

---

As you can see, there are 4 columns:

1. HouseholdID - this is what I would like the ability to roll-up to
2. HouseholdMemberID - this is a unique identifier for each record in the dataset (eg. If I am in HouseholdID = 1 and I am the 4th member, my unique value would be 1.4)
3. Name - this is a vanilla name field
4. AnnualIncome (at the HouseholdMemberID level)

---

With this dataset, I would like to have the ability to start with this view (at the HouseholdLevel):

For example, we can see that Household1 has an AnnualIncome of $102,050, which is calculated by summing the AnnualIncome for each member of HouseholdID = 1:

• Man - $50,000
• Woman - $50,000
• Boy - $2,000
• Girl - $50

---

Additionally, i'd like the ability to be able to show the data as follows:

...to be able to see how each Household Member contributes to the AnnualIncome of the Household.

---

For the purposes of this example, I used Grouping in Excel to manually accomplish this task; however, I would like to make use of dynamic grouping. In other words, i'd like to be able to import a dataset that has these identical columns each month, but with varying numbers of households (and household members). How can I do this?

Any help is greatly appreciated! Thanks!

Answer 1

Use PivotTables. My normal data is in a normal table:

And with PivotTables. I resume the data exactly as you need:

UPDATE ANSWER. With an additional click. I can use also the Field NAME so i get more data as you want. It just takes about 0.00000003 seconds to do it.