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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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.
byundtMechanical EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.