Results 1 to 4 of 4

Thread: Looking for a formula to help group/count complicated data

  1. #1

    Looking for a formula to help group/count complicated data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I work with lists of data that have thousands of data entries in the list. I need to be able to group all of the similar data together and then generate a count for each group. The problem that I'm having is that some of the data is slightly different from each other, but I still want it to be in the same category. For example, this is what my list might look like.

    Dog
    Dogs
    White dog
    Cat
    cat
    cats

    All of that data is slightly different from each other, yet you can still tell that all the top data should go into one category together for dogs, and all the bottom data should go into another category for cats. Is there any way to have a formula to get this type of data grouped together and then counted? Thanks for your help!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,233
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm curious... how many unique entries are there? You could run a formula down to look for "Dog" and "Cat" to check if it exists in the cell, but if you have hundreds of possibilities, that could take some time...

    I'm just trying to figure out the best way to approach this. Could you post a larger sample of what you're looking at? (You can add a workbook to the post by double clicking the "Reply to Thread" button.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    (You can add a workbook to the post by double clicking the "Reply to Thread" button.
    I didn't know that! It works at VBAX too

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,233
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Bob Phillips View Post
    I didn't know that! It works at VBAX too
    Wow! I think I just earned my weekend off! It's not every day that I get to teach you something, Bob!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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