Hello,
I have this table in columns A to F
[TABLE="class: grid, width: 544"]
<tbody>[TR]
[TD="align: center"]File1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Record[/TD]
[TD] ID[/TD]
[TD] ABKs[/TD]
[TD] MNKs[/TD]
[TD] Date[/TD]
[TD] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]972567676[/TD]
[TD="align: center"]34305[/TD]
[TD="align: center"]72358[/TD]
[TD="align: center"] 3/4/2019 22:13[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]685206308[/TD]
[TD="align: center"]8198[/TD]
[TD="align: center"]27174[/TD]
[TD="align: center"] 3/4/2019 22:16[/TD]
[TD="align: center"]61[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]393064[/TD]
[TD="align: center"]10093118[/TD]
[TD="align: center"] 3/4/2019 22:18[/TD]
[TD="align: center"]238[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]431153[/TD]
[TD="align: center"]10055124[/TD]
[TD="align: center"] 3/4/2019 22:22[/TD]
[TD="align: center"]232[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]394506[/TD]
[TD="align: center"]10091831[/TD]
[TD="align: center"] 3/4/2019 22:25[/TD]
[TD="align: center"]239[/TD]
[/TR]
[TR]
[TD="align: center"]File2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]494676[/TD]
[TD="align: center"]9992073[/TD]
[TD="align: center"] 3/4/2019 22:29[/TD]
[TD="align: center"]307[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]480117[/TD]
[TD="align: center"]10005787[/TD]
[TD="align: center"] 3/4/2019 22:35[/TD]
[TD="align: center"]326[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]500751[/TD]
[TD="align: center"]9986284[/TD]
[TD="align: center"] 3/4/2019 22:53[/TD]
[TD="align: center"]74[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]174754[/TD]
[TD="align: center"]10312153[/TD]
[TD="align: center"] 3/4/2019 22:54[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]File3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]734660[/TD]
[TD="align: center"]9751476[/TD]
[TD="align: center"] 3/4/2019 23:04[/TD]
[TD="align: center"]79[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]1307025[/TD]
[TD="align: center"]9178944[/TD]
[TD="align: center"] 3/4/2019 23:05[/TD]
[TD="align: center"]256[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]530935[/TD]
[TD="align: center"]9955441[/TD]
[TD="align: center"] 3/4/2019 23:18[/TD]
[TD="align: center"]41[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]564462[/TD]
[TD="align: center"]9921311[/TD]
[TD="align: center"] 3/4/2019 23:19[/TD]
[TD="align: center"]713[/TD]
[/TR]
[TR]
[TD="align: center"]File4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]941774677[/TD]
[TD="align: center"]441381[/TD]
[TD="align: center"]10044548[/TD]
[TD="align: center"] 3/4/2019 23:46[/TD]
[TD="align: center"]1196[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]941774677[/TD]
[TD="align: center"]336354[/TD]
[TD="align: center"]7138685[/TD]
[TD="align: center"] 3/5/2019 0:06[/TD]
[TD="align: center"]839[/TD]
[/TR]
[TR]
[TD="align: center"]File5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]1303422672[/TD]
[TD="align: center"]1947830[/TD]
[TD="align: center"]8538344[/TD]
[TD="align: center"] 3/5/2019 0:30[/TD]
[TD="align: center"]126[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]1303422672[/TD]
[TD="align: center"]939494[/TD]
[TD="align: center"]2130082[/TD]
[TD="align: center"] 3/5/2019 0:33[/TD]
[TD="align: center"]107[/TD]
[/TR]
</tbody>[/TABLE]
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 in image below
I've set the pivot table as shown below.
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
I have this table in columns A to F
[TABLE="class: grid, width: 544"]
<tbody>[TR]
[TD="align: center"]File1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Record[/TD]
[TD] ID[/TD]
[TD] ABKs[/TD]
[TD] MNKs[/TD]
[TD] Date[/TD]
[TD] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]972567676[/TD]
[TD="align: center"]34305[/TD]
[TD="align: center"]72358[/TD]
[TD="align: center"] 3/4/2019 22:13[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]685206308[/TD]
[TD="align: center"]8198[/TD]
[TD="align: center"]27174[/TD]
[TD="align: center"] 3/4/2019 22:16[/TD]
[TD="align: center"]61[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]393064[/TD]
[TD="align: center"]10093118[/TD]
[TD="align: center"] 3/4/2019 22:18[/TD]
[TD="align: center"]238[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]431153[/TD]
[TD="align: center"]10055124[/TD]
[TD="align: center"] 3/4/2019 22:22[/TD]
[TD="align: center"]232[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]394506[/TD]
[TD="align: center"]10091831[/TD]
[TD="align: center"] 3/4/2019 22:25[/TD]
[TD="align: center"]239[/TD]
[/TR]
[TR]
[TD="align: center"]File2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]494676[/TD]
[TD="align: center"]9992073[/TD]
[TD="align: center"] 3/4/2019 22:29[/TD]
[TD="align: center"]307[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]480117[/TD]
[TD="align: center"]10005787[/TD]
[TD="align: center"] 3/4/2019 22:35[/TD]
[TD="align: center"]326[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]500751[/TD]
[TD="align: center"]9986284[/TD]
[TD="align: center"] 3/4/2019 22:53[/TD]
[TD="align: center"]74[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]300264531[/TD]
[TD="align: center"]174754[/TD]
[TD="align: center"]10312153[/TD]
[TD="align: center"] 3/4/2019 22:54[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]File3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]734660[/TD]
[TD="align: center"]9751476[/TD]
[TD="align: center"] 3/4/2019 23:04[/TD]
[TD="align: center"]79[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]1307025[/TD]
[TD="align: center"]9178944[/TD]
[TD="align: center"] 3/4/2019 23:05[/TD]
[TD="align: center"]256[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]530935[/TD]
[TD="align: center"]9955441[/TD]
[TD="align: center"] 3/4/2019 23:18[/TD]
[TD="align: center"]41[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]725372898[/TD]
[TD="align: center"]564462[/TD]
[TD="align: center"]9921311[/TD]
[TD="align: center"] 3/4/2019 23:19[/TD]
[TD="align: center"]713[/TD]
[/TR]
[TR]
[TD="align: center"]File4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]941774677[/TD]
[TD="align: center"]441381[/TD]
[TD="align: center"]10044548[/TD]
[TD="align: center"] 3/4/2019 23:46[/TD]
[TD="align: center"]1196[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]941774677[/TD]
[TD="align: center"]336354[/TD]
[TD="align: center"]7138685[/TD]
[TD="align: center"] 3/5/2019 0:06[/TD]
[TD="align: center"]839[/TD]
[/TR]
[TR]
[TD="align: center"]File5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Record[/TD]
[TD="align: center"] ID[/TD]
[TD="align: center"] ABKs[/TD]
[TD="align: center"] MNKs[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] Seg[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]1303422672[/TD]
[TD="align: center"]1947830[/TD]
[TD="align: center"]8538344[/TD]
[TD="align: center"] 3/5/2019 0:30[/TD]
[TD="align: center"]126[/TD]
[/TR]
[TR]
[TD="align: center"]NewRecord[/TD]
[TD="align: center"]1303422672[/TD]
[TD="align: center"]939494[/TD]
[TD="align: center"]2130082[/TD]
[TD="align: center"] 3/5/2019 0:33[/TD]
[TD="align: center"]107[/TD]
[/TR]
</tbody>[/TABLE]
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 in image below
I've set the pivot table as shown below.
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
Last edited: