This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
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.
It's the nature of a pivot table to group items and if replace X=1, Y=2 Z=3 in your sample layout and have a running date, we can simply see the problem:
We have 3 DEF in the source, but 2 of them have A=2 and B=2 just with a different date. The Pivot must group them and it does as you can see in I7, it shows 2 days for this item.
If you want to separate the items, you have to take the date into account:
Andreas.
Hi,
In a simple table, please show the exact expected result. For clarity, show actual Date/time entries in the last column.
Data set with datetime examples:
Column A || Column B || Column C || Column D
==============================
ABC || Value X || Value Z || 8/18/2019 03:52
ABC || Value X || Value X || 8/20/2019 17:40
DEF || Value Y || Value Y || 12/31/2018 23:11
DEF || Value Y || Value Z || 1/10/2019 11:01
DEF || Value Y || Value Y || 1/27/2019 09:34
GHI || Value Z || Value X || 12/31/2018 02:22
GHI || Value Z || Value Z || 12/31/2018 19:49
JKL || Value X || Value Z || 02/18/2019 07:39
JKL || Value X || Value Y || 02/18/2019 07:45
JKL || Value X || Value X || 02/18/2019 07:53
MNO || Value Z || Value Y || 04/02/2020 04:46
MNO || Value Z || Value Z || 04/03/2020 08:19
Expected outcome in PT:
Rows
Column B
Column A
Column C
Values
Column D
Resulting Pivot
+Value X
+ABC
Value Z 8/18/2019 03:52
Value X 8/20/2019 17:40
+JKL
Value Z 02/18/2019 07:39
Value Y 02/18/2019 07:45
Value X 02/18/2019 07:53
+Value Y
+DEF
Value Y 12/31/2018 23:11
Value Z 1/10/2019 11:01
Value Y 1/27/2019 09:34
+Value Z
+GHI
Value X 12/31/2018 02:22
Value Z 12/31/2018 19:49
+MNO
Value Y 04/02/2020 04:46
Value Z 04/03/2020 08:19
Actual Outcome in PT:
Rows
Column B
Column A
Column C
Values
Column D
Resulting Pivot
+Value X
+ABC
Value Z 8/18/2019 03:52
Value X 8/20/2019 17:40
+JKL
Value Z 02/18/2019 07:39
Value Y 02/18/2019 07:45
Value X 02/18/2019 07:53
+Value Y
+DEF
Value Y 12/31/2018 23:11
Value Z 1/10/2019 11:01
+Value Z
+GHI
Value X 12/31/2018 02:22
Value Z 12/31/2018 19:49
+MNO
Value Y 04/02/2020 04:46
Value Z 04/03/2020 08:19
If I look at the PT with the hierarchy groupings when I do this, I get:
+Value X
+ABC
+Value Z
8/18/2019 03:52
+Value X
8/20/2019 17:40
+JKL
+Value Z
02/18/2019 07:39
+Value Y
02/18/2019 07:45
+Value X
02/18/2019 07:53
+Value Y
+DEF
+Value Y
12/31/2018 23:11
1/27/2019 09:34
+Value Z
1/10/2019 11:01
+Value Z
+GHI
+Value X
12/31/2018 02:22
+Value Z
12/31/2018 19:49
+MNO
+Value Y
04/02/2020 04:46
+Value Z
04/03/2020 08:19
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. I don't know, maybe this is the wrong way to organize this information? It's hard to do it in a straight table because there's much more information than I have specified here - I have 30 columns of data and 626 rows with 247 unique records. I'm trying to view at a quick glance which records need to be looked at more in depth based on values that have changed over time. Records where the value has changed and then were changed back are especially important.