+ Reply to Thread
Results 1 to 6 of 6

How to group matching values on a spread sheet?

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    How to group matching values on a spread sheet?

    I have a large spread sheet with over 6000 product lines. I've attached a sample of data.

    I need to group products together so that I can see the total number of products and value according to the type of product involved.

    Eg on the sample spreadsheet I want the spread sheet to show the total number of "3003" products and "1002" products (with the option to then breakdown the total number into inidividual lines as per the current spreadsheet). As you can see from the sample - I've manually entered the totals i need to see in columns J & K.

    I seem to think I can use "Groups" to do this but it's not something I've ever used so am looking for the easiest way to do this. If a simple formula can do the job then again that would be great.

    If you can provide any suggestions in "an idiot's guide" format it would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to group matching values on a spread sheet?

    Looks like a perfect job for a Pivot Table - Click a cell in the range - Insert Ribbon - Click Pivot Table and set Product ref as row field and Stock Value as data field - Play around with the PT, you can do lots of things in the blink of an eye.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to group matching values on a spread sheet?

    hi grim72. you can do a subtotal. go to Data -> Subtotal -> At each change in: Product ref
    Use function: Sum
    Add subtotal to:
    Qty
    Stock Value

    is that something you can use?

    ps: click on the 1,2,3 on the extreme top left to group or ungroup
    Attached Files Attached Files
    Last edited by benishiryo; 10-29-2012 at 08:30 AM. Reason: added file

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: How to group matching values on a spread sheet?

    Thanks, I'm rubbish at pivot tables - any chance you could set it up on the attached spreadsheet so I can then adapt it into my actual spreadsheet?

  5. #5
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: How to group matching values on a spread sheet?

    Again, if possible could you adapt the sample spreadsheet to show me how this works? Excuse my ignorance.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to group matching values on a spread sheet?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1