How to SHOW duplicate data in Pivot Table Groups

Question

Hi folks, I found only one other post out there that asked this question, and it was for Excel 2003 and said this was only possible if the person upgraded to 2010. I have 2016 and can't figure this out. Every other post or explainer about this has been about how to REMOVE duplicates, which is not my issue. I did find this (https://support.office.com/en-us/article/repeat-item-labels-in-a-pivottable-882bdb55-9cdc-4d8d-b531-8e96e41dea31) but when I tried it, it did not seem to work for me.

I am working with source data that is duplicate in nature. I'm trying to track changes in a particular value over time. Column A is the identifier for the record, Column B is the final value, and Column C is all the values ever selected for that record. Column D is a unique date-time stamp of when the value in Column C was selected.

Column A || Column B || Column C || Column D

==============================

ABC          || Value X     || Value Z     || Datetime

ABC          || Value X     || Value X     || Datetime

DEF          || Value Y     || Value Y     || Datetime

DEF          || Value Y     || Value Z     || Datetime

DEF          || Value Y     || Value Y     || Datetime

GHI          || Value Z     || Value X     || Datetime

GHI          || Value Z     || Value Z     || Datetime

JKL           || Value X     || Value Z     || Datetime

JKL           || Value X     || Value Y     || Datetime

JKL           || Value X     || Value X     || Datetime

MNO       || Value Z     || Value Y      || Datetime

MNO       || Value Z     || Value Z      || Datetime

I pulled all of this information into a pivot table with grouped levels, Column B at the top, then Column A, then Column C with Column D as the value. I'm trying to easily see what values where changed and in what order they were selected based on the final value chosen. The hierarchy displayed using the Pivot table is essential to this.

My issue is with records like I have in the example above for DEF. Over time, the same value, Value Y, was selected twice. The pivot table only shows it to me once, and with only one of the two date-times from Column D. I need it to show me both. I cannot for the life of me figure this out. Any help would be appreciated. Thanks.

Answer 1

Not really sure if I get it right, but I'll give it try anyway. Perhaps you need to create the PT with the following settings.

Column A and C as "Rows"

Column B as "Columns"

and Column D as "Values", count.

Then it will look like this.

Hi, this succeeds in telling me how many times the value was selected, but I'm trying to see the order over time that they were selected in. So I don't think this will work, but thank you for your reply.

Answer 2

Which, while this succeeds in displaying all the dates, it doesn't allow me to see the values from Column C in datetime order, which is what I'm trying to achieve.

The point is that this is not logical in the meaning of the data.

You can not group first and then sort by date, because it is mutually exclusive. Have a look what happens if we sort by date, then B, then A, then C (left table is the source, right table the result):

You have the 3 of B at the top and the bottom. Try a usual sort resp. use Power Query to transform your data, IMHO you'll understand what's going on.

Andreas.

Answer 3

Hi,

Does this help?  You may download my solution workbook from here.