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

1. ## 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. ## 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.

3. ## 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...

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

Originally Posted by tigeravatar
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. ## Re: 3 columns: None contain unique values, but I need a list of every unique set

Originally Posted by FDibbins
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. ## 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. ## 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. ## 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?

@fdibbins

10. ## 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

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

#### 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