Lookup Unique based on Multiple Conditions

**statenja** · 01-02-2009, 02:13 PM

I am looking for a solution other than using an advanced data filter for unique records only.

I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).

Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.

Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?

SEE ATTACHED:

Thanks,

**shg** · 01-02-2009, 02:53 PM

Why the prepossession against advanced filter?

**Ron Coderre** · 01-02-2009, 03:02 PM

If you don't mind having an Item Count....Have you considered using a Pivot Table?

See the edited version of your posted file (attached) for examples.

Does that help?
Post back with more questions.

**statenja** · 01-02-2009, 04:59 PM

To answer SHG's question "Why the prepossession against advanced filter? "

If the tables were created automatically after the paste operation, there would only be the paste step. Using the advanced filter the user would have to paste, click on advanced filter, unique record and then sort by checksheet and then paste the applicable rows into each table. This increases the possibility for user error, especially since the people using the spreadsheet don't all have a great deal of excel knowledge.

To answer Ron's question "what about using a pivot table?"

I like that possibility because that is only one click to refresh the source data, but it creates another challenge. After the unique tables are created, I am using each of the values (team,checksheet,record#,finding#) to perform a lookup function. With the pivot table if there are multiple Record #'s with the same Team and Checksheet, then the pivot table makes blank cells after the first record. (cells G12, H12, M12, N12, O12, M14 in Ron's attached file) When there are blank cells, the lookup function does not work.

I am not sure if it is possible to perform this operation in one step or not, but I thought it would be worthwhile to ask. Thank you very much for your responses!!!

**Steve R** · 01-02-2009, 07:11 PM

Hold your horses!

Check out the file attached.

**martindwilson** · 01-02-2009, 08:20 PM

Cor blimey!!!!!!!!

**statenja** · 01-06-2009, 11:22 AM

Steve R,

Thanks for the reply. The solution you provided seems to do just what I was looking for. I am having some trouble converting this solution into my real spreadsheet.

and I am having some great difficulty understanding how this function works. Could you provide some insight about how this function works, so that I will be able to get a better grasp on the way it works. I noticed that the formula seems to be the exact same for all cells in each table (group a and b)?

**Ron Coderre** · 01-06-2009, 12:14 PM

I attached a formula based solution that uses "helper" cells to reduce the complexity of the formulas.

For each Group:
One cell counts the number of unique items that exist for that group.
Other cells identify the row numbers for those unique items.

Is that something you can work with?