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

Posted on 2014-08-02
Last Modified: 2016-03-02

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.
Question by:WeThotUWasAToad
    LVL 80

    Accepted 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.
    LVL 80

    Expert Comment

    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.
    LVL 80

    Expert Comment

    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))),"")

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now