Multiple Rows per Category, Making a PivotTable

[Khax]

Hi all,

I am trying to figure out how to make a pivot table where there are multiple items (rows) associated with a single location (column).

For example a single hospital may purchase multiple types of medical devices. How do I create a pivot table that understands to group all of the different items under a single hospital, and do this for multiple hospitals?

Since I am a new user it will not allow me to post an image. For example cell A1 contains "XYZ Hospital". Cell b1 contains "Knee Replacements", cell b2 contains "Hip Replacements. Cell b3 contains (Rotator Cuff Repair", etc. Cells a2 through a3 are blank, as the name of the hospital appears only once in cell a1, and you can see visually that cells b1:b7: contain items associated with the hospital from cell a1. I hope this makes sense.

I want to be able to create a pivot table, filter by hospital, and have each item the hospital purchased appear in the pivot table. Currently, only the item b1 (the same row as the hospital name) appears.

Thanks for your help with this one.

[Olly]

Welcome to the forum

This will be much more effective if we demonstrate using your data, rather than talking in general principles.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Khax]

Hi Olly,

Apologies -- I've attached a sample of what I am attempting to do here. Essentially, I'd like the filter in the "Pivot Table Mockup" tab to be able to filter by hospital to include each item, not just the one in the cell to the right of it, as you can see is the case in the "Pivot Table" tab.

I know it will look a little different than the mockup if I am able to do it correctly, but that is as close as I can get it manually.

Thanks for the help.

[Olly]

To pivot data effectively, your source data needs to be normalised - that is, each row contains a fact (or facts), and all the dimension values which describe that fact.

In your case, your source data has a blank value for the location dimension, in many cases.

I'd suggest using Power Query (Get & Transform data) to transform (normalise) your source data, then you can create your pivot table based on that. You just need a fairly simple query to fill down the Location values, and remove the Items subtotal rows.

Format your source data as a table, then use:

Please Login or Register  to view this content.

Now pivot on this. See attached workbook for an example.