Microsoft Excel: How do you group a list of companies/account numbers that are duplicated on several rows

25 pts.
Tags:
Microsoft Excel
How do you group a list of companies/account numbers that are duplicated on several rows (separate purchases) to identify companies that purchased specific products within 2 code ranges (c50-c509, c18-c20.) and include ALL of the that companies purchase rows regardless of the product code in that row, then sort the entire worksheet to place only those companies (and all their rows) who purchased those items within the specified code ranges on the top of the sheet to cut and paste onto a new excel sheet?

Software/Hardware used:
Excel 2010

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Jaideep Khanduja
    Including all company rows irrespective of the desired product code range filtering will not produce any meaningful results.
    16,890 pointsBadges:
    report
  • ToddN2000
    So it sounds like if a company bought a specific product code you want to sell of that companies purchase. Do you want just I row for each company with a summary of all sales?
    89,455 pointsBadges:
    report
  • Csimon1

    Sorry for the confusion. I want to identify the companies that purchased a product within the 2 code ranges (c50-c509,c18-c20) and then group ALL of their purchase rows together on the sheet, and then sort the sheet so that all of those companies & all of their purchase rows are at the top of the sheet to easily cut and paste onto another sheet for separate processing. The summary of sales is not important in this scenario. Here's a mock example below. Since Littman's purchased an item (c50.012) within the specified product range c50-c509 on row 1, they met the purchase code criteria. They also purchased another item on row 2 that didn't meet the criteria, but I still need to have that purchase row grouped with row 1 to show all the purchases made by that company. Note: Russellman's and Gnipperdogs did not purchase an item within the product code criteria, so I'm not interested in those companies. I then want to sort the entire sheet and place only those companies with all of their purchase rows at the top of the sheet. I hope this description and mock scenario is a bit clearer. Thanks!!

    ACCOUNT NUMBERCOMPANY PRODUCT CODEDESCRIPTION OF PRODUCT 
    951891LITTMANSC50.012HAMMER
    951891LITTMANSC90.01BOOK MARKER
    983994MAYERONIC18.9SCREW DRIVER
    983994MAYERONID32.0TELEPHONE
    6119300RUSSELLMANSC91.10BALL
    7180772GNIPPERDOGSC92.11GLOVE
    8215343BELOVEDSD32.0TELEPHONE
    8215343BELOVEDSC18.9SCREW DRIVER
    25 pointsBadges:
    report
  • ToddN2000
    Sounds like you could do it with a script. Build a table of account numbers if they have a sale that meets your selection criteria. Then read through that table and select all matching sales records for that account regardless if the product met your selection and load that to a new table or spreadsheet. The sort the table/sheet by account or name or what ever you need.
    89,455 pointsBadges:
    report
  • Csimon1

    Thanks for the suggestion. I've never created a script before.

    Any help that could be provided by the Excel experts out there is appreciated. 

    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: