CODEWITHSUNDEEP
excelpivot-table
Ger Cas
Ger Cas

Reputation: 2298

How to get uniques IDs and totals with Pivot Table Excel

I have this table in columns A to F

    A     |      B       |     C     |     D      |        E         |   F     |
---------------------------------------------------------------------------------
File1      |              |           |            |                  |
Record     |  ID          |  ABKs     |  MNKs      |  Date            |  Seg
NewRecord  |  972567676   |  34305    |  72358     |  3/4/2019 22:13  |  21
NewRecord  |  685206308   |  8198     |  27174     |  3/4/2019 22:16  |  61
NewRecord  |  300264531   |  393064   |  10093118  |  3/4/2019 22:18  |  238
NewRecord  |  300264531   |  431153   |  10055124  |  3/4/2019 22:22  |  232
NewRecord  |  300264531   |  394506   |  10091831  |  3/4/2019 22:25  |  239
File2      |              |           |            |                  |  
Record     |  ID          |  ABKs     |  MNKs      |  Date            |  Seg
NewRecord  |  300264531   |  494676   |  9992073   |  3/4/2019 22:29  |  307
NewRecord  |  300264531   |  480117   |  10005787  |  3/4/2019 22:35  |  326
NewRecord  |  300264531   |  500751   |  9986284   |  3/4/2019 22:53  |  74
NewRecord  |  300264531   |  174754   |  10312153  |  3/4/2019 22:54  |  19
File3      |              |           |            |                  |  
Record     |  ID          |  ABKs     |  MNKs      |  Date            |  Seg
NewRecord  |  725372898   |  734660   |  9751476   |  3/4/2019 23:04  |  79
NewRecord  |  725372898   |  1307025  |  9178944   |  3/4/2019 23:05  |  256
NewRecord  |  725372898   |  530935   |  9955441   |  3/4/2019 23:18  |  41
NewRecord  |  725372898   |  564462   |  9921311   |  3/4/2019 23:19  |  713
File4      |              |           |            |                  |  
Record     |  ID          |  ABKs     |  MNKs      |  Date            |  Seg
NewRecord  |  941774677   |  441381   |  10044548  |  3/4/2019 23:46  |  1196
NewRecord  |  941774677   |  336354   |  7138685   |  3/5/2019 0:06   |  839
File5      |              |           |            |                  |  
Record     |  ID          |  ABKs     |  MNKs      |  Date            |  Seg
NewRecord  |  1303422672  |  1947830  |  8538344   |  3/5/2019 0:30   |  126
NewRecord  |  1303422672  |  939494   |  2130082   |  3/5/2019 0:33   |  107

I want to create a pivot table that shows me the data as mentionen in Expected Pivot Table format but I'm currently getting the output as shown in Current Pivot Table output enter link description here

I've set the pivot table as shown below. enter link description here

Is possible to get the table desired with pivot table? and what I need to do for such result?

Attached sample excel file

Sample1.xslx

Upvotes: 0

Views: 48

Answers (1)

Gaurav Jain
Gaurav Jain

Reputation: 28

Just drag PivotField/Column {Date} from "values" to "Rows" in Pivot It will affect your column sequence.

Upvotes: 0

Related Questions