Excel Data clean up for Pivot table

peter8848

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


QtyBRAND 1BRAND 2BRAND 3BRAND 4BRAND 5BRAND 6BRAND 7BRAND 8
GROUP 1GROUP 2GROUP 3GROUP 4GROUP 5GROUP 6GROUP 7GROUP 8
Product Name 1Product Name 2Product Name 3Product Name 4Product Name 5Product Name 6Product Name 7Product Name 8
DATE 19072283256397961
DATE 210085599995898549
DATE 333977921427510035
DATE 42070438681943256
DATE 57241479387346624
DATE 66766706466437869
DATE 78468993477579160
DATE 82626462384579173
SalesBRAND 1BRAND 2BRAND 3BRAND 4BRAND 5BRAND 6BRAND 7BRAND 8
GROUP 1GROUP 2GROUP 3GROUP 4GROUP 5GROUP 6GROUP 7GROUP 8
Product Name 1Product Name 2Product Name 3Product Name 4Product Name 5Product Name 6Product Name 7Product Name 8
DATE 1 372.00 277.00 303.00 332.00 358.00 420.00 285.00 779.00
DATE 2 936.00 573.00 630.00 449.00 989.00 761.00 594.00 362.00
DATE 3 640.00 301.00 551.00 895.00 377.00 285.00 392.00 428.00
DATE 4 329.00 524.00 809.00 644.00 282.00 944.00 467.00 831.00
DATE 5 899.00 802.00 752.00 340.00 851.00 429.00 605.00 892.00
DATE 6 871.00 967.00 942.00 204.00 309.00 745.00 614.00 977.00
DATE 7 797.00 900.00 404.00 992.00 933.00 292.00 338.00 885.00
DATE 8 545.00 652.00 568.00 940.00 925.00 703.00 259.00 623.00
Price BRAND 1 BRAND 2 BRAND 3 BRAND 4 BRAND 5 BRAND 6 BRAND 7 BRAND 8
GROUP 1 GROUP 2 GROUP 3 GROUP 4 GROUP 5 GROUP 6 GROUP 7 GROUP 8
Product Name 1 Product Name 2 Product Name 3 Product Name 4 Product Name 5 Product Name 6 Product Name 7 Product Name 8
DATE 1 4.13 3.85 10.82 10.38 6.39 10.77 3.61 12.77
DATE 2 9.36 6.74 10.68 4.54 10.41 8.55 6.99 7.39
DATE 3 19.39 3.10 6.97 42.62 8.98 3.80 3.92 12.23
DATE 4 16.45 7.49 18.81 7.49 3.48 10.04 14.59 14.84
DATE 5 12.49 19.56 16.00 3.66 9.78 12.62 9.17 37.17
DATE 6 13.00 14.65 13.46 3.19 4.68 17.33 7.87 14.16
DATE 7 9.49 13.24 4.08 29.18 12.12 5.12 3.71 14.75
DATE 8 20.96 25.08 12.35 40.87 11.01 12.33 2.85 8.53

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like this?

PivotTable
Date(All)
ProductSum of PriceSum of QtySum of Sales
Product Name 1
105.27​
492​
5389​
Product Name 2
93.71​
525​
4996​
Product Name 3
93.17​
471​
4959​
Product Name 4
141.93​
452​
4796​
Product Name 5
66.85​
588​
5024​
Product Name 6
80.56​
488​
4579​
Product Name 7
52.71​
622​
3554​
Product Name 8
121.84​
427​
5777​
Grand Total
756.04
4065
39074
 

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
100
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)

Product Name 11/07/2018Price90
Product Name 12/07/2018Price100
Product Name 13/07/2018Price33
Product Name 14/07/2018Price20
Product Name 15/07/2018Price72
Product Name 16/07/2018Price67
Product Name 17/07/2018Price84
Product Name 18/07/2018Price26
Product Name 21/07/2018Price72
Product Name 22/07/2018Price85
Product Name 23/07/2018Price97
Product Name 24/07/2018Price70
Product Name 25/07/2018Price41
Product Name 26/07/2018Price66
Product Name 27/07/2018Price68
Product Name 28/07/2018Price26

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


not (Wide DATA)

Product Name 1Product Name 2
1/07/2018Price9072
2/07/2018Price10085
3/07/2018Price3397
4/07/2018Price2070
5/07/2018Price7241
6/07/2018Price6766
7/07/2018Price8468
8/07/2018Price2626

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>



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

thanks
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
100

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,569
Messages
5,625,575
Members
416,119
Latest member
JCLLE

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
Top