+ Reply to Thread
Results 1 to 10 of 10

3 columns: None contain unique values, but I need a list of every unique set

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    3 columns: None contain unique values, but I need a list of every unique set

    Hi guys,

    I've been dominating Excel functions for about a week now, but for the last three days I haven't been able to solve this problem:

    I have a huge dataset that takes up about 10,000 rows in Excel. Here's a small version of what it looks like:

    Last First Account
    Black Gary Group
    Black Gary Group
    Miller John TypeA
    Miller Jane Simple2
    Miller John&Jane Group
    Miller John&Jane Group
    Smith Zoey Group
    Smith Greg Crazy3
    Smith Greg Crazy3
    Smith Gary Group

    As you can see:

    -None of the three columns is very identifiable because many last names, first names, and account types are repeated. In fact, the only "uniques" are the items in the 3rd column that aren't called "Group"--no one but Greg Smith will ever have an account called Crazy3.

    -Each row (in the 10,000 row document) has many repeats.
    Ex:
    Miller John&Jane Group
    Miller John&Jane Group


    The problem:
    1. I want a unique list of each set of 3 where the right-most column = Group. No duplicates.
    Ex:
    Black Gary Group
    Miller John&Jane Group
    Smith Zoey Group
    Smith Gary Group

    I've tried just about every combination of functions...
    Ex: INDEX( , MATCH( , INDEX(COUNTIF( blah blah blah

    2. BONUS: Later, I will also need to know how to create a unique list of EVERY set of three (whether the last value = Group or not). If you can help with this too, it would be really useful.
    Ex:
    Black Gary Group
    Miller John TypeA
    Miller Jane Simple2
    Miller John&Jane Group
    Smith Zoey Group
    Smith Greg Crazy3
    Smith Gary Group

    ********I have Excel 03 by the way.***********

    Thank you thank you thank you thank you. It'd make my job so much easier if you could help me solve this.

    -mathematician

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    mathematican,

    Welcome to the forum!
    What you've described is exactly what Advanced Filter was created for.

    [EDIT]
    Expanding a bit more.
    For your 1st point, you would have an advanced filter criteria of Account = Group. To set that up, use a separate column (say column E) and have a cell (in this case E1) that contains the header Account. Then, in the cell immediately below it (so E2) type in Group. Then, when you apply the advanced filter, use E1:E2 as the filter criteria.

    For your 2nd point, this is actually easier because you are not using any filter criteria, so leave that field blank.
    Last edited by tigeravatar; 06-14-2012 at 06:16 PM.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    see if the attached gives you a start on what you want...
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    Quote Originally Posted by tigeravatar View Post
    mathematican,

    Welcome to the forum!
    What you've described is exactly what Advanced Filter was created for.

    [EDIT]
    Expanding a bit more.
    For your 1st point, you would have an advanced filter criteria of Account = Group. To set that up, use a separate column (say column E) and have a cell (in this case E1) that contains the header Account. Then, in the cell immediately below it (so E2) type in Group. Then, when you apply the advanced filter, use E1:E2 as the filter criteria.

    For your 2nd point, this is actually easier because you are not using any filter criteria, so leave that field blank.

    I don't know whether to be happy or annoyed at how easy that is. I will try this at work tomorrow and let you know if it works. I suspect it will.

    Thank you so much for the warm welcome. I don't mind this Excel stuff so you might find me might be lurking around this forum for a little while.

  5. #5
    Registered User
    Join Date
    06-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    Quote Originally Posted by FDibbins View Post
    see if the attached gives you a start on what you want...
    Thanks! Haven't looked at it yet b/c the computer I'm on doesn't have Excel, but I'll take a look at it during work tomorrow!

  6. #6
    Registered User
    Join Date
    06-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    It all worked! Thanks. I gave you a star.

  7. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    I'm glad it worked for you, and thank you for the rep

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    @ mathetitian. Im sorry that you had a problem downloading the file i attached, it was neither jibberish nor did it contain a virus as you suggested (the server has its own virus protection to prevent that). I downloaded the same file from the link in here and it loaded without a problem. I dont believe i deserved the negative rating you gave me, but thats your choice

    Perhaps some1 else could try and d/l the file and see what results they get?

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    @fdibbins

    No problem with the download of your workbook.

    You give fine advice how to work with your solutions.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: 3 columns: None contain unique values, but I need a list of every unique set

    thanks for the feedback, oeldere. the comments were from the OP in thier feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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