asked on

Build a short list based on criteria applied to long list

Experts, ok so I have a partial list of products in Col A (result set).
There are many duplicate products in the result set (which is ok)

In Col B I have a master list of all products (no duplicates here).

In Col C I have a formula that counts how many times each of the products in the complete list appears in the result set.

Now I want to build a unduplicated short list of just those products from the master list that occur one or more times in Col A.

In SQL no problem, I would just select distinct Col A from Worksheet

Or I would Select Col B from Worksheet where Col C > 0

How do I achieve the same results using a formula in Excel (without any sql)?

Thanks in advance.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

If you have Excel 2010 or later, you can use the AGGREGATE function in a formula that doesn't need to be array-entered:
=IFERROR(INDEX(A$1:A$100,AGGREGATE(15,6,MATCH(B$1:B$1000,A$1:A$100,0),ROWS(C$1:C1))),"")

Naresh Patel

what if ? if list Column A is master & Column B is common list & want to find values in column C which is not in B but it is in A? list view?

Thanks

Naresh Patel

if you go ahead better read this - I had use this formula

=IF(ISNA(VLOOKUP(A2,$B$2:$B$500,1,FALSE)),A2,"")

Open in new window

but is not like list view. & this is not my question, some asked & I solved but he wants in list view & I cant .....so if you feel so pls provide me ....