Excel Data clean up for Pivot table

P

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
112
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]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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]
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Ranking with NET and with in the group
Replies
7
Views
109
sksanjeev786
S
JenniferMurphy
  • Solved
Why is this one CountIfs expression getting a Value error?
Replies
4
Views
765
JenniferMurphy
JenniferMurphy
JenniferMurphy
  • Solved
How to compare 2 columns in one table with 2 columns in another table?
Replies
5
Views
361
JenniferMurphy
JenniferMurphy
V
  • Question Question
Group list by batches based on multiple criteria
Replies
4
Views
418
hagia_sofia
hagia_sofia
S
  • Solved
Require the heading date in which column found the first data/value in a specific row.
Replies
4
Views
66
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top