Excel Group Columns

Posted on 2014-08-08
Last Modified: 2014-08-28
I have an Excel Spreadsheet (Excel 2003) and I need to filter out some records based on 2 columns
For example Column B I want to filter out all records that are of a specific value for example "A539"
Then on top of that I also want to filter out on those records that are in column L that are blank.
Once I have filtered out the records I want to Group on Column G, Group on Column H, then give me the sum of values in  Column I

for example the attached excel spread sheet is you can assume I have already run a filter to get me to the stage of showing those that are A539 and are blank in column L.

I am trying to get it so that the results will be

Black L  22
Black M 1
Beige M 2
Beige L 7
Question by:GenieMaster
    LVL 27

    Expert Comment

    by:Glenn Ray
    This sure sounds like a job for Pivot Tables!

    You could create a Pivot Table from your master data, then set the Report Filters to "A539" for your column B field and "(blank)" for the column L field.  

    You'd then make the fields for column G and H the Row Labels and the values in column I as the Values.
    Example set up
    If you could provide a larger set of sample data - unfiltered - I could send you an example file with a Pivot Table.

    LVL 27

    Accepted Solution


    Did you have any additional questions or issues with the solution I provided?  If so, let me know.

    Otherwise, please properly close this question by clicking the "Accept this solution" link above my previous post.  That will ensure that the answer is available to others searching for a similar solution and that points are awarded.

    With Thanks,

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    746 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

    13 Experts available now in Live!

    Get 1:1 Help Now