Pivot table problem (connected cells)

Question

Hello,I am currently facing a problem with my pivot table in Excel. The data (base) table that I am using has some columns with connected cells and others that are separated. For the analysis, I need the pivot table to assign all singular cells of column A to the connected cells of column B. However, at the moment, the Pivot table only assigns the first cell element from column A to the connected cell element of column B.Separating the connected cells will not solve the problem, because&nbsp;I need the pivot to assign all corresponding elements (cells) in column A for the one element in column B. Is there a solution to this problem?Thank you already.Greetings,Zoe

mathetes · Answer

zoe_we&nbsp;&nbsp;Can't quite visualize what you're describing. Is it possible for you to post a copy of the file, if not here in this forum then on OneDrive or GoogleDrive or equivalent. Grant access and post a link here.

zoe_we · Answer

mathetes&nbsp;Thanks for the quick reply. I created a simple case example in Excel. I hope this explains the problem better. When you download the file as an xlsx file, you should be able to access the pivot table.&nbsp;&nbsp;Google Drive Excel Sheet&nbsp;

hansvogelaar · Answer

zoe_we&nbsp;
You'll have to unmerge C9:C10 and repeat the value "Apple" in C10. Otherwise, your source data is not a real database-like table.
&nbsp;

zoe_we · Answer

HansVogelaarYes, but is there another solution to assigning the properties (sweet &amp; sour) to Apple? Because otherwise my amount of Apples in the table becomes untrue. The real table I am using is much bigger and the amount of entities in column B is of importance.

hansvogelaar · Answer

zoe_we&nbsp;
Add the source range to the data model when you create the pivot table:

You can then select Distinct Count as aggregation function for the value field:

Result:

Forum Discussion

Pivot table problem (connected cells)

Share

Resources