+ Reply to Thread
Results 1 to 2 of 2

Group 3500 items based on another table?

  1. #1
    Registered User
    Join Date
    02-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    26

    Group 3500 items based on another table?

    I have a file with 3500 lines in column A.

    Like this:
    PEN
    PEN
    PEN
    PAPER
    TAPE
    TAPE
    ERASER
    TOILETPAPER
    TOILETBRUSH
    TOILETBRUSH
    Ö

    I can convert to table and pivottable and get how many PEN there is and how many TAPE and so on.

    However, I want to group them.
    Like PEN and ERASER = Office material
    And TOILETPAPER and TOILETBRUSH = Bathroom

    The problem is that itís 3500 rows, and there is a total of 850 available products. Some exist like 100 times and some arenít used.

    I have a table on another sheet like:
    Office material Bathroom Group X
    Pen Toiletpaper X
    Eraser Toiletbrush Y

    Can I group my 3500 items in an easy way?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,663

    Re: Group 3500 items based on another table?

    It can be done.

    But you'd likely need to flatten your data in "another sheet".

    You can then load both to PowerQuery and use merge/Join operation (or use Index/Match or Vlookup formula in sheet).

    Another alternative is to load both to data model and then use data relationship.

    If you need specific help, I'd recommend uploading sample workbook with sanitized data.
    ďProgress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.Ē
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 12
    Last Post: 04-01-2016, 03:57 PM
  2. Do not group similar items together - PIVOT TABLE
    By sourabhg98 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-10-2016, 10:55 AM
  3. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  4. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  5. Replies: 9
    Last Post: 02-01-2011, 08:47 AM
  6. group items in pivot table
    By minkus in forum Excel General
    Replies: 1
    Last Post: 09-30-2007, 08:35 PM
  7. Can't group pivot table items by month in Excel
    By scott_ensley in forum Excel General
    Replies: 1
    Last Post: 02-01-2005, 05:06 PM

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