Pivot table problem (connected cells)

[zoe_we]

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 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

[#2]

@zoe_we 

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.

[#3]

@mathetes 

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. 

Google Drive Excel Sheet 

[#4]

@zoe_we 

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.

[#5]

@Hans Vogelaar
Yes, but is there another solution to assigning the properties (sweet & 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.

[#6]

@zoe_we 

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: