Creating a list of unique items, which fit multiple criteria

jadnich

New Member
Joined
Sep 1, 2015
Messages
11
Hello,

This is my first post looking for assistance. I have found the majority of what I need by searching forums and running Google searches, but I have gotten stuck on a project, and I hope you can help. In general, I want a drop down menu of unique item names which fit multiple criteria, pulled from a constantly updating list of purchases that may not necessarily be unique.

I have a table that contains a number of data entries. For the purposes of my question, I am concerned with the first three columns. 1- a list of items purchased, 2- a category for these items, and 3- a subcategory.

An example of the list of purchases- Monitor, monitor, camera, recorder, camera, monitor, etc.
the Categories are- in house, remote, and storage
the subcategories are - Primary and Accessory

I am trying to create a list of unique items that fit criteria in both the category and subcategory. I want it to read "Monitor, Camera, Recorder" and only list items that are "In House" and "Primary". The result of this list will be used in a drop-down menu that will dynamically adjust as new relevant data is added to the primary list.

first, I have created a table with my primary data entry (Table3, as my sheet refers to it). This table extends dynamically as new items are purchased so they are included in the next step.

I then created a second table (Table4) which uses the following formula:
{=IFERROR(INDEX(Table3[Purchases],MATCH(0,COUNTIF($M$3:M3,Table3[Purchases]),0)),"")} where M3 is the column header for this new table and the list of unique items appears below. I use ctrl+shift+enter to ensure the array formula calculates correctly.

This gives me a list of unique items exactly as I expected, but it also includes items that have categories and subcategories I don't want to include. The items in this list are the named group from which my drop down menu will come.

Is there something I could add to my formula that would only return unique purchased items that are In House and Primary items?



------sub-problem: as new items are added to Table3, and thus to Table4, they appear in the correct column but the Table4 boundry does not dynamically adjust the size for entries that are not manually entered. The result of this is that new items do not appear on the drop down list. To solve this, I have dragged Table4 down a few extra spaces and added the IFERROR function to replace #N/A with blank spaces, and will have to manage the document to ensure there are always blanks for the table to grow in to. is there a better way to do this, so that the drop down list always stays up to date?


I would appreciate any help you could provide, and if any missing information is needed, I will fill in the blanks. Thank you.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Unfortunately, I can't upload files from behind my firewall at work. But I will try my best to simplify. One of the main problems I have had in finding an answer to this question is that the trouble is in the details.

I want to create a list of unique item names, for which I used {=IFERROR(INDEX(Table3[Purchases],MATCH(0,COUNTIF($M$3:M3,Table3[Purchases]),0)),"")} and built a table out of the resulting list.

This table contains only unique item names, but I want to limit the items that appear on that list to only those that fit a certain category and subcategory from the source table. Is there a function I can add in to the formula that will accomplish this? Is there another way I should be looking at this problem?

Once again, thank you for the help. And I apologize for the lengthy description. One of the issues I have had in searching solutions on the web is that many of the solutions are all formula based, and they don't have use-cases to compare with my own. My description is useful to people who think like me, and probably less useful to a subject-matter expert.
 
Upvote 0
See if this example helps

Data in columns A:C; criteria in columns E:F; Unique list in column G


A
B
C
D
E
F
G
1
Items​
Category​
Subcategory​
Category​
Subcategory​
List​
2
Monitor​
In House​
Primary​
In House​
Primary​
Monitor​
3
Camera​
Remote​
Primary​
Item1​
4
Recorder​
Storage​
Accessory​
Item3​
5
Monitor​
In House​
Primary​
6
Camera​
Remote​
Primary​
7
Recorder​
Storage​
Accessory​
8
Item1​
In House​
Primary​
9
Item2​
In House​
Accessory​
10
Item3​
In House​
Primary​
11
Item1​
In House​
Primary​
12

Array formula in G2 copied down
=IFERROR(INDEX($A:$A,SMALL(IF(FREQUENCY(IF($A$2:$A$100<>"",IF($B$2:$B$100=$E$2,IF($C$2:$C$100=$F$2,MATCH($A$2:$A$100,$A$2:$A$100,0)))),ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)),ROWS(G$2:G2))),"")
Ctrl+Shift+Enter

Hope this helps

M.
 
Upvote 0
The same example using structured references with a table
Assumes table name = Table3


A
B
C
D
E
F
G
1
Purchases​
Category​
Subcategory​
Category​
Subcategory​
List​
2
Monitor​
In House​
Primary​
In House​
Primary​
Monitor​
3
Camera​
Remote​
Primary​
Item1​
4
Recorder​
Storage​
Accessory​
Item3​
5
Monitor​
In House​
Primary​
6
Camera​
Remote​
Primary​
7
Recorder​
Storage​
Accessory​
8
Item1​
In House​
Primary​
9
Item2​
In House​
Accessory​
10
Item3​
In House​
Primary​
11
Item1​
In House​
Primary​
12

<tbody>
</tbody>


Array formula in G2 copied down
=IFERROR(INDEX(Table3[Purchases],SMALL(IF(FREQUENCY(IF(Table3[Purchases]<>"",IF(Table3[Category]=$E$2,IF(Table3[Subcategory]=$F$2,MATCH(Table3[Purchases],Table3[Purchases],0)))),ROW(Table3[Purchases])-MIN(ROW(Table3[Purchases]))+1),ROW(Table3[Purchases])-MIN(ROW(Table3[Purchases]))+1),ROWS(G$2:G2))),"")

Ctrl+Shift+Enter

M.
 
Upvote 0
Thank you, Marcelo. This worked like a charm. It will take some time for me to unpack what is going on in this formula, but I have a good general idea.


The same example using structured references with a table
Assumes table name = Table3


A
B
C
D
E
F
G
1
Purchases​
Category​
Subcategory​
Category​
Subcategory​
List​
2
Monitor​
In House​
Primary​
In House​
Primary​
Monitor​
3
Camera​
Remote​
Primary​
Item1​
4
Recorder​
Storage​
Accessory​
Item3​
5
Monitor​
In House​
Primary​
6
Camera​
Remote​
Primary​
7
Recorder​
Storage​
Accessory​
8
Item1​
In House​
Primary​
9
Item2​
In House​
Accessory​
10
Item3​
In House​
Primary​
11
Item1​
In House​
Primary​
12

<tbody>
</tbody>


Array formula in G2 copied down
=IFERROR(INDEX(Table3[Purchases],SMALL(IF(FREQUENCY(IF(Table3[Purchases]<>"",IF(Table3[Category]=$E$2,IF(Table3[Subcategory]=$F$2,MATCH(Table3[Purchases],Table3[Purchases],0)))),ROW(Table3[Purchases])-MIN(ROW(Table3[Purchases]))+1),ROW(Table3[Purchases])-MIN(ROW(Table3[Purchases]))+1),ROWS(G$2:G2))),"")

Ctrl+Shift+Enter

M.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top