[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Convert report with an excessive number of rows to a more concise format in Excel


Fig. 1 below shows the format displayed by the freeware website-platform Magento when the following report is created from the dashboard:

        Reports > Products > Products Ordered

For each month, an individual row is created for each product with one or more sales. Products with no sales that month are not shown. Needless to say, this format utilizes an excessive number of rows and is therefore cumbersome to analyze and manipulate.

After downloading the report (as a CSV file) and saving it as an .xls file, what formula or method would be best for converting the data to the more concise table-like format shown in Fig. 2?


PS Please provide solutions using only Excel formulas (ie without using VBA) if that is possible.
  • 3
1 Solution
The simplest method would be to make a PivotTable. Your CSV file is already normalized, so you would just need to do the following (assuming Excel 2013):
1. Select any cell in your data in the CSV file
2. Open the Insert...PivotTable menu item
3. You will probably want to put the PivotTable report in a new worksheet
4. Drag "Product" into the Rows box
5. Drag "Qty" into the Values box
6. Drag "Date into the Columns box

There are some differences between the PivotTable appearance and your desired report format. If those differences are important, I can give you additional steps to customize the PivotTable report--but my hope is that it won't be necessary.
You could alternatively use a formula like:
=IFERROR(1/(1/SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,$A5,Sheet1!$A:$A,B$3 & "-" & B$4)),"")

The above formula requires Excel 2007 or later for the SUMIFS function. It assumes that the dates in Sheet1 column A are stored as text rather than as a date time serial number that looks like text.

Should the latter assumption not be correct, then you could use a formula like:
=IFERROR(1/(1/SUMIFS('Alt Sheet1'!$C:$C,'Alt Sheet1'!$B:$B,$A5,'Alt Sheet1'!$A:$A,--(B$4 & " 1, " & B$3))),"")

The first formula is shown on worksheet Formulas, while the second is shown on worksheet Alt Formulas in the workbook attached.
I noticed that the data and report were in different rows and columns than your screenshots. I inserted blank rows and columns to align the worksheets to match in the attached file. As a result, the formulas will be slightly different.
=IFERROR(1/(1/SUMIFS(Sheet1!$D:$D,Sheet1!$C:$C,$B6,Sheet1!$B:$B,C$4 & "-" & C$5)),"")
=IFERROR(1/(1/SUMIFS('Alt Sheet1'!$D:$D,'Alt Sheet1'!$C:$C,$B6,'Alt Sheet1'!$B:$B,--(C$5 & " 1, " & C$4))),"")
WeThotUWasAToadAuthor Commented:
Thanks Brad. I've heard of pivot tables many times in the past but have never understood their actual function until now. Thanks for your excellent response including the detailed steps.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now