Consolidating the details from a file

[xlbaby]

Hi All,

I have a document which have the following details.

Data Available.PNG

I need the data in the below format:

Data I need.PNG

Can you please help me understand how I can I achieve it?

[alansidman]

Use the subtotal function built in to Excel. Then collapse the groups. See attached.

[xlbaby]

Thank you alansidman for the resolution. However, what will I do if the data available is not in any order. Please see below.

Data Unsorted.PNG

[alansidman]

Sort the data prior to running the sub total function.

[xlbaby]

Thank you. This works and thank you for your help.

[alansidman]

you are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[oeldere]

with a pivot table after rearange the data (e.g. with power query).

see the attached file.