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.
 

jadnich

New Member
Joined
Sep 1, 2015
Messages
11
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

jadnich

New Member
Joined
Sep 1, 2015
Messages
11
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.
 

Forum statistics

Threads
1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top