Transposing, Filtering Data... kinda...

I'm new to this site and just discovering the magical world of VBA and Macros.

Just a brief summary: I work for a company that often does inspections on buildings. Some of the reports we do, we must include the same data sorted in several different ways. I have to list every Room and each material and the quantity of it for every room. Then further in the report, i have to list the materials and what rooms they are in. For many of these buildings there are 50+ rooms and 50+ different materials. The materials and quantities vary from room to room, so we use a master table with all of the materials and rooms pre listed so as we walk through, we can mark off quantities if they are in the rooms.

I attached a small example of what our typical spreadsheets look like and the products we need for our final reports. Hope i'm looking in the right place. If not please direct me to where to look.
Thanks!

Hi,

You can use INDEX MATCH arrays.

Enter in C14 and copy down.

Please Login or Register  to view this content.

Enter in D21 and copy down.

Please Login or Register  to view this content.

These are arrays, use CTRL, SHIFT, ENTER when entering them.

See attachment.

Hope this is helpful.

Cheers