Excel Data clean up for Pivot table

[peter8848]

Dear All,

I have a three table of data which I would like to use Pivot table to see the sales or qty or price by brand, group,product and date. However, it is not use friendly yet so what is the best way to convert them into a pivot table use friendly data? Or do I use power BI or Power Pivot will help? Thanks in advance.

Cheers,

Peter


[TABLE="width: 912"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD]Qty[/TD]
[TD]BRAND 1[/TD]
[TD]BRAND 2[/TD]
[TD]BRAND 3[/TD]
[TD]BRAND 4[/TD]
[TD]BRAND 5[/TD]
[TD]BRAND 6[/TD]
[TD]BRAND 7[/TD]
[TD]BRAND 8[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]GROUP 2[/TD]
[TD]GROUP 3[/TD]
[TD]GROUP 4[/TD]
[TD]GROUP 5[/TD]
[TD]GROUP 6[/TD]
[TD]GROUP 7[/TD]
[TD]GROUP 8[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD]Product Name 2[/TD]
[TD]Product Name 3[/TD]
[TD]Product Name 4[/TD]
[TD]Product Name 5[/TD]
[TD]Product Name 6[/TD]
[TD]Product Name 7[/TD]
[TD]Product Name 8[/TD]
[/TR]
[TR]
[TD]DATE 1[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]DATE 2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]DATE 3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]DATE 4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]DATE 5[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]DATE 6[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]DATE 7[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]DATE 8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]BRAND 1[/TD]
[TD]BRAND 2[/TD]
[TD]BRAND 3[/TD]
[TD]BRAND 4[/TD]
[TD]BRAND 5[/TD]
[TD]BRAND 6[/TD]
[TD]BRAND 7[/TD]
[TD]BRAND 8[/TD]
[/TR]
[TR]
[TD]GROUP 1[/TD]
[TD]GROUP 2[/TD]
[TD]GROUP 3[/TD]
[TD]GROUP 4[/TD]
[TD]GROUP 5[/TD]
[TD]GROUP 6[/TD]
[TD]GROUP 7[/TD]
[TD]GROUP 8[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD]Product Name 2[/TD]
[TD]Product Name 3[/TD]
[TD]Product Name 4[/TD]
[TD]Product Name 5[/TD]
[TD]Product Name 6[/TD]
[TD]Product Name 7[/TD]
[TD]Product Name 8[/TD]
[/TR]
[TR]
[TD]DATE 1[/TD]
[TD] 372.00[/TD]
[TD] 277.00[/TD]
[TD] 303.00[/TD]
[TD] 332.00[/TD]
[TD] 358.00[/TD]
[TD] 420.00[/TD]
[TD] 285.00[/TD]
[TD] 779.00[/TD]
[/TR]
[TR]
[TD]DATE 2[/TD]
[TD] 936.00[/TD]
[TD] 573.00[/TD]
[TD] 630.00[/TD]
[TD] 449.00[/TD]
[TD] 989.00[/TD]
[TD] 761.00[/TD]
[TD] 594.00[/TD]
[TD] 362.00[/TD]
[/TR]
[TR]
[TD]DATE 3[/TD]
[TD] 640.00[/TD]
[TD] 301.00[/TD]
[TD] 551.00[/TD]
[TD] 895.00[/TD]
[TD] 377.00[/TD]
[TD] 285.00[/TD]
[TD] 392.00[/TD]
[TD] 428.00[/TD]
[/TR]
[TR]
[TD]DATE 4[/TD]
[TD] 329.00[/TD]
[TD] 524.00[/TD]
[TD] 809.00[/TD]
[TD] 644.00[/TD]
[TD] 282.00[/TD]
[TD] 944.00[/TD]
[TD] 467.00[/TD]
[TD] 831.00[/TD]
[/TR]
[TR]
[TD]DATE 5[/TD]
[TD] 899.00[/TD]
[TD] 802.00[/TD]
[TD] 752.00[/TD]
[TD] 340.00[/TD]
[TD] 851.00[/TD]
[TD] 429.00[/TD]
[TD] 605.00[/TD]
[TD] 892.00[/TD]
[/TR]
[TR]
[TD]DATE 6[/TD]
[TD] 871.00[/TD]
[TD] 967.00[/TD]
[TD] 942.00[/TD]
[TD] 204.00[/TD]
[TD] 309.00[/TD]
[TD] 745.00[/TD]
[TD] 614.00[/TD]
[TD] 977.00[/TD]
[/TR]
[TR]
[TD]DATE 7[/TD]
[TD] 797.00[/TD]
[TD] 900.00[/TD]
[TD] 404.00[/TD]
[TD] 992.00[/TD]
[TD] 933.00[/TD]
[TD] 292.00[/TD]
[TD] 338.00[/TD]
[TD] 885.00[/TD]
[/TR]
[TR]
[TD]DATE 8[/TD]
[TD] 545.00[/TD]
[TD] 652.00[/TD]
[TD] 568.00[/TD]
[TD] 940.00[/TD]
[TD] 925.00[/TD]
[TD] 703.00[/TD]
[TD] 259.00[/TD]
[TD] 623.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD] BRAND 1 [/TD]
[TD] BRAND 2 [/TD]
[TD] BRAND 3 [/TD]
[TD] BRAND 4 [/TD]
[TD] BRAND 5 [/TD]
[TD] BRAND 6 [/TD]
[TD] BRAND 7 [/TD]
[TD] BRAND 8 [/TD]
[/TR]
[TR]
[TD] GROUP 1 [/TD]
[TD] GROUP 2 [/TD]
[TD] GROUP 3 [/TD]
[TD] GROUP 4 [/TD]
[TD] GROUP 5 [/TD]
[TD] GROUP 6 [/TD]
[TD] GROUP 7 [/TD]
[TD] GROUP 8 [/TD]
[/TR]
[TR]
[TD] Product Name 1 [/TD]
[TD] Product Name 2 [/TD]
[TD] Product Name 3 [/TD]
[TD] Product Name 4 [/TD]
[TD] Product Name 5 [/TD]
[TD] Product Name 6 [/TD]
[TD] Product Name 7 [/TD]
[TD] Product Name 8 [/TD]
[/TR]
[TR]
[TD]DATE 1[/TD]
[TD] 4.13[/TD]
[TD] 3.85[/TD]
[TD] 10.82[/TD]
[TD] 10.38[/TD]
[TD] 6.39[/TD]
[TD] 10.77[/TD]
[TD] 3.61[/TD]
[TD] 12.77[/TD]
[/TR]
[TR]
[TD]DATE 2[/TD]
[TD] 9.36[/TD]
[TD] 6.74[/TD]
[TD] 10.68[/TD]
[TD] 4.54[/TD]
[TD] 10.41[/TD]
[TD] 8.55[/TD]
[TD] 6.99[/TD]
[TD] 7.39[/TD]
[/TR]
[TR]
[TD]DATE 3[/TD]
[TD] 19.39[/TD]
[TD] 3.10[/TD]
[TD] 6.97[/TD]
[TD] 42.62[/TD]
[TD] 8.98[/TD]
[TD] 3.80[/TD]
[TD] 3.92[/TD]
[TD] 12.23[/TD]
[/TR]
[TR]
[TD]DATE 4[/TD]
[TD] 16.45[/TD]
[TD] 7.49[/TD]
[TD] 18.81[/TD]
[TD] 7.49[/TD]
[TD] 3.48[/TD]
[TD] 10.04[/TD]
[TD] 14.59[/TD]
[TD] 14.84[/TD]
[/TR]
[TR]
[TD]DATE 5[/TD]
[TD] 12.49[/TD]
[TD] 19.56[/TD]
[TD] 16.00[/TD]
[TD] 3.66[/TD]
[TD] 9.78[/TD]
[TD] 12.62[/TD]
[TD] 9.17[/TD]
[TD] 37.17[/TD]
[/TR]
[TR]
[TD]DATE 6[/TD]
[TD] 13.00[/TD]
[TD] 14.65[/TD]
[TD] 13.46[/TD]
[TD] 3.19[/TD]
[TD] 4.68[/TD]
[TD] 17.33[/TD]
[TD] 7.87[/TD]
[TD] 14.16[/TD]
[/TR]
[TR]
[TD]DATE 7[/TD]
[TD] 9.49[/TD]
[TD] 13.24[/TD]
[TD] 4.08[/TD]
[TD] 29.18[/TD]
[TD] 12.12[/TD]
[TD] 5.12[/TD]
[TD] 3.71[/TD]
[TD] 14.75[/TD]
[/TR]
[TR]
[TD]DATE 8[/TD]
[TD] 20.96[/TD]
[TD] 25.08[/TD]
[TD] 12.35[/TD]
[TD] 40.87[/TD]
[TD] 11.01[/TD]
[TD] 12.33[/TD]
[TD] 2.85[/TD]
[TD] 8.53[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

something like this?

PivotTable
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7](All)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Product[/td][td=bgcolor:#DDEBF7]Sum of Price[/td][td=bgcolor:#DDEBF7]Sum of Qty[/td][td=bgcolor:#DDEBF7]Sum of Sales[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 1[/td][td]

105.27​

[/td][td]

492​

[/td][td]

5389​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 2[/td][td]

93.71​

[/td][td]

525​

[/td][td]

4996​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 3[/td][td]

93.17​

[/td][td]

471​

[/td][td]

4959​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 4[/td][td]

141.93​

[/td][td]

452​

[/td][td]

4796​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 5[/td][td]

66.85​

[/td][td]

588​

[/td][td]

5024​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 6[/td][td]

80.56​

[/td][td]

488​

[/td][td]

4579​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 7[/td][td]

52.71​

[/td][td]

622​

[/td][td]

3554​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Product Name 8[/td][td]

121.84​

[/td][td]

427​

[/td][td]

5777​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

756.04​

[/td][td=bgcolor:#DDEBF7]

4065​

[/td][td=bgcolor:#DDEBF7]

39074​

[/td][/tr]
[/table]

 

[peter8848]

Hi Sandy,

Sorry with the pivot table we cannot use wide data right? We can only use long data?

I meant

we can use below (Long DATA)

[TABLE="width: 340"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]1/07/2018[/TD]
[TD]Price[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]2/07/2018[/TD]
[TD]Price[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]3/07/2018[/TD]
[TD]Price[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]4/07/2018[/TD]
[TD]Price[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]5/07/2018[/TD]
[TD]Price[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]6/07/2018[/TD]
[TD]Price[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]7/07/2018[/TD]
[TD]Price[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]Product Name 1[/TD]
[TD="align: right"]8/07/2018[/TD]
[TD]Price[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]1/07/2018[/TD]
[TD]Price[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]2/07/2018[/TD]
[TD]Price[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]3/07/2018[/TD]
[TD]Price[/TD]
[TD]97[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]4/07/2018[/TD]
[TD]Price[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]5/07/2018[/TD]
[TD]Price[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]6/07/2018[/TD]
[TD]Price[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]7/07/2018[/TD]
[TD]Price[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]Product Name 2[/TD]
[TD="align: right"]8/07/2018[/TD]
[TD]Price[/TD]
[TD]26
[/TD]
[/TR]
</tbody>[/TABLE]


not (Wide DATA)

[TABLE="width: 343"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Product Name 1[/TD]
[TD]Product Name 2[/TD]
[/TR]
[TR]
[TD="align: right"]1/07/2018[/TD]
[TD]Price[/TD]
[TD]90[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD="align: right"]2/07/2018[/TD]
[TD]Price[/TD]
[TD]100[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD="align: right"]3/07/2018[/TD]
[TD]Price[/TD]
[TD]33[/TD]
[TD]97[/TD]
[/TR]
[TR]
[TD="align: right"]4/07/2018[/TD]
[TD]Price[/TD]
[TD]20[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD="align: right"]5/07/2018[/TD]
[TD]Price[/TD]
[TD]72[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD="align: right"]6/07/2018[/TD]
[TD]Price[/TD]
[TD]67[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD="align: right"]7/07/2018[/TD]
[TD]Price[/TD]
[TD]84[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD="align: right"]8/07/2018[/TD]
[TD]Price[/TD]
[TD]26[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]



Right? So how will we quickly convert the wide data into long data?

thanks

 

[sandy666]

I assumed you want use PivotTable but I see you don't
could you post expected result from your first post?

edit:
or better use PowerQuery
transform each table as you wish then merge (or append) and load to the sheet

 

[peter8848]

Hi Sandy,

Sorry about the confusion, Yes I want to use the pivot table but if I used the wide data i just found it was not that easy to use as Long data as shown above. Now i figure it out myself for the above to pivot table. But just confirm, for the wide data shown above, could we have more row or column labels for pivot table formation? say adding day, holiday on the column next to date OR add product brand or category in the rows next to product name row. Would it work?

Cheers,

Peter

 

[sandy666]

First of all, your tables are incorrect. The table should have a single header row and possibly labels.
Your vision of PivotTable cuts functionality such as TotalSum / TotalCount and some more, but it's up to you what you want to get.
That's why I asked for an example of how the result should look like

and again, use PowerQuery to prepare your tables like you want (all with the same layout if you want collapse them together) then use (or not) PivotTable

 

[sandy666]

maybe it will help:

• 23 things you should know about Excel Pivot Tables
• How Do Pivot Tables Work?