Sep 19 2022 05:36 AM
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
Sep 19 2022 05:49 AM
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.
Sep 19 2022 07:01 AM
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.
Sep 19 2022 07:14 AM
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.
Sep 19 2022 07:19 AM
Sep 19 2022 07:32 AM
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: