Dynamic List of Unique Items Based on Criteria

[Maddy1234]

Hello wise people,

I need some help on a specific query. I'm trying out a simple BI to help manage the employees queue work rates.

There are two types of queues; Production and non- Production. Production Codes will have volumes ranging from 0 to any number and the non production codes will have "-". Please see below.

[TABLE="width: 358"]
<tbody>[TR]
[TD]Queue[/TD]
[TD]Time Taken[/TD]
[TD="align: center"]Volumes[/TD]
[/TR]
[TR]
[TD]Production Queue 1[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Production Queue 2[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]Meal Break[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD]Production Queue 2[/TD]
[TD="align: center"]1:30[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Staff Meeting[/TD]
[TD="align: center"]0:20[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD]Production Queue 1[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Production Queue 2[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]System Down[/TD]
[TD="align: center"]0:10[/TD]
[TD="align: center"]-[/TD]
[/TR]
</tbody>[/TABLE]

I want to dynamically create two simple separate lists for Production and non production. Please see below for desired result.

[TABLE="width: 360"]
<tbody>[TR]
[TD]Production[/TD]
[TD][/TD]
[TD]Non Production[/TD]
[/TR]
[TR]
[TD]Production Queue 1[/TD]
[TD][/TD]
[TD]Meal Break[/TD]
[/TR]
[TR]
[TD]Production Queue 2[/TD]
[TD][/TD]
[TD]Staff Meeting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]System Down[/TD]
[/TR]
</tbody>[/TABLE]

I do not want to use pivot tables and I would appreciate if someone could help me achieve the desire result without the use of Ctrl+Shift+Enter.

Many thanks in advance.

 

[wideboydixon]

Hey. Pivot tables are not required but I can't think of a way to do this without an array formula like this:

Book1
	A	B	C	D	E	F
1	Queue	Time Taken	Volumes		Production	Non Production
2	Production Queue 1	01:00	1		Production Queue 1	Meal Break
3	Production Queue 2	00:30	0		Production Queue 2	Staff Meeting
4	Meal Break	00:45	-			System Down
5	Production Queue 2	01:30	2
6	Staff Meeting	00:20	-
7	Production Queue 1	01:00	2
8	Production Queue 2	00:45	1
9	System Down	00:10	-
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($A$2:$A$9,MATCH(0,IF($C$2:$C$9<>"-",COUNTIF($E$1:$E1,$A$2:$A$9),""),0)),"")}
F2	{=IFERROR(INDEX($A$2:$A$9,MATCH(0,IF($C$2:$C$9="-",COUNTIF($F$1:$F1,$A$2:$A$9),""),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[FormR]

Hi, here is an option that does not require CSE - note though that these are still array processing formula with the associated negative impact of using large ranges and on top of that will probably be less efficient than the CSE entered alternatives .

Excel 2013/2016
	A	B	C	D	E	F
1	Queue	Time Taken	Volumes		Prod	non prod
2	Production Queue 1	01:00:00	1		Production Queue 1	Meal Break
3	Production Queue 2	00:30:00	0		Production Queue 2	Staff Meeting
4	Meal Break	00:45:00	-			System Down
5	Production Queue 2	01:30:00	2
6	Staff Meeting	00:20:00	-
7	Production Queue 1	01:00:00	2
8	Production Queue 2	00:45:00	1
9	System Down	00:10:00	-
Sheet1

Cell Formulas
Range	Formula
E2	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/((MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($A$2:$A$9)-ROW($A$2)+1)*ISNUMBER($C$2:$C$9)),ROWS(E$2:E2))),"")
F2	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/((MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($A$2:$A$9)-ROW($A$2)+1)*($C$2:$C$9="-")),ROWS(F$2:F2))),"")

 

[Maddy1234]

Thank you both so much. Both your solutions work perfectly. Many thanks guys. You rock!

 

[markmzz]

Another way (with Wideboydixon's layout):

In E2 and copy down

=IFERROR(INDEX(A$2:A$9,MATCH(0,INDEX(COUNTIF(E$1:E1,A$2:A$9)+(C$2:C$9="-"),),0)),"")

In F2 and copy down

=IFERROR(INDEX(A$2:A$9,MATCH(0,INDEX(COUNTIF(F$1:F1,A$2:A$9)+(C$2:C$9<>"-"),),0)),"")

Ps: not for a large number of records.

Markmzz