Welcome to this tutorial on learning how to correctly format your data to prepare for import within the system.
In this tutorial, we are going to take a file that is formatted like this:
And we’re going to turn it into a file that can be imported into the system.
We are currently working on implementing a feature that will make this automatic, but until that point. Here is what to do:
First, open the file in excel and name it "Raw Data." Next, open up a new worksheet and name it “Sales Unpivot”
In the “Raw Data” sheet, change the names of the months/years to the word Sales and then a number. 1 being your most recent sales and the highest number being your oldest sales.
For example, in my csv, my most recent sales numbers are in January of 2020, so I will rename this to Sales1. I will then rename December 2019 to Sales2. A quick way to do this is to click and hold the bottom right corner of the box and drag all the way to the left to automatically number my sales months.
I end up with January 2017 becoming sales 37. This means that I have 37 months of sales history.
While in the sales unpivot sheet, go to the insert tab and select the pivot table.
Now return to the data sheet, select all of the data and click okay.
You’ll notice a list of check boxes that appear on the right hand side of the screen, check SKU and location, then select all of the sales.
You should see all of the sum of sales begin to appear in the values column.
Once you have finished selecting all of the sales, drag the values box into the rows column underneath SKU, location, and values.
With the pivot table selected, switch to the design tab. Now, click report layout, then select show in tabular form.
Click report layout again and select repeat all item labels. Now click subtotals and select do not show subtotals. Now click grand totals and select off for rows and columns.
Now insert two new rows at the top by selecting row 1 and 2 next to the pivot table then right clicking, now select insert.
Put the words "Today’s Date" in E1 and the actual numbered date in F1
Then put the words sales month in E3 and the word date in F3
Now in column E4 put this formula: =RIGHT(C4,LEN(C4)-7)
You should see your oldest sales month appear. Because I had 37 sales months, Sales37 appeared.
Now, double click the bottom right corner of the green box. The formula for E5 should be the same except C4 should be replaced with C5, so on and so forth.
In column F4 put the following formula: =DATE(YEAR($F$1),MONTH($F$1) - (RIGHT(E4,LEN(E4)-5)), DAY($F$1)) Now double click the bottom right corner of F4 to duplicate this formula.
Now create a new sheet and name it Sales. Copy the 4 necessary columns: SKU, Location, Units Sold, and Date. You may need to add a new heading and delete the top 2 rows of data, and format your date properly, but now you have your file formatted correctly.
You can now open up a blank workbook and copy this data into it. save this file as a csv and it will be ready to import into the system!