Dynamic List Of Unique Items based on Multiple Criteria

[Maddy1234]

Hello experts,

I have the following the dataset.

Queue	Start Time	End Time	Production	Volumes
Queue 1	19:00:00	20:00:00	P	2
Comfort Break	20:00:00	20:10:00	N	-
Queue 2	20:10:00	21:30:00	P	3
Team Meeting	21:30:00	22:00:00	N	-
Queue 1	22:00:00	22:15:00	P	1
Queue 3	22:15:00	23:30:00	P	5
Staff Reviews	23:30:00	0:10:00	N	-
Queue 4	0:10:00	0:40:00	P	2
Meal Break	0:40:00	1:30:00	N	-
Queue 2	1:30:00	2:00:00	P	1

<colgroup><col width="111" style="width: 83pt;"><col width="99" style="width: 74pt;"><col width="91" style="width: 68pt;"><col width="103" style="width: 77pt;"><col width="88" style="width: 66pt;"></colgroup><tbody>
</tbody>

And FormR has helped me achieve the following result with the given code.

Result :

Production

Queue 1

Queue 2

Queue 3

Queue 4

<colgroup><col width="103" style="width: 77pt;"></colgroup><tbody>
</tbody>

Code :

=INDEX($A$2:$A$99,AGGREGATE(15,6,(ROW($A$2:$A$99)-ROW($A$2)+1)/((MATCH($A$2:$A$99,$A$2:$A$99,0)=ROW($A$2:$A$99)-ROW($A$2)+1)*($D$2:$D$99="P")),ROWS(E$1:E1)))

Result :

Non Production

Comfort Break

Team Meeting

Staff Reviews

Meal Break

<colgroup><col width="135" style="width: 101pt;"></colgroup><tbody>
</tbody>

Code :

=INDEX($A$2:$A$99,AGGREGATE(15,6,(ROW($A$2:$A$99)-ROW($A$2)+1)/((MATCH($A$2:$A$99,$A$2:$A$99,0)=ROW($A$2:$A$99)-ROW($A$2)+1)*($D$2:$D$99="N")),ROWS(F$1:F1)))

This works brilliantly however I have a new criteria requirement. I need to separate the non production table into further 2 categories like below.

Desired result :

Non Production		Breaks
Team Meeting		Meal break
Staff Reviews		Comfort Break

<colgroup><col width="135" style="width: 101pt;"><col width="87" style="width: 65pt;"><col width="112" style="width: 84pt;"></colgroup><tbody>
</tbody>

Breaks are limited to Meal Break, Comfort Break and Sick Leave. Non production codes can vary and is not limited to a list rather it will be marked with a "N" in Column D in the dataset.

Any help will be hugely appreciated.

Thank you.

 

[Maddy1234]

Is there anyone who can help please?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Queue	Start Time	End Time	Production	Volumes		4​	2​	2​
2​	Queue 1	19:00:00​	20:00:00​	P​	2​		production	non-production: no break	non-production: break
3​	Comfort Break	20:00:00​	20:10:00​	N​	-​		Queue 1	Team Meeting	Comfort Break
4​	Queue 2	20:10:00​	21:30:00​	P​	3​		Queue 2	Staff Reviews	Meal Break
5​	Team Meeting	21:30:00​	22:00:00​	N​	-​		Queue 3
6​	Queue 1	22:00:00​	22:15:00​	P​	1​		Queue 4
7​	Queue 3	22:15:00​	23:30:00​	P​	5​
8​	Staff Reviews	23:30:00​	0:10:00​	N​	-​
9​	Queue 4	0:10:00​	0:40:00​	P​	2​
10​	Meal Break	0:40:00​	1:30:00​	N​	-​
11​	Queue 2	1:30:00​	2:00:00​	P​	1​

Define the following as referring to respective ranges:

Queue
STime
ETime
Production
Volums

and

Ivec in the Name Manager as referring to:

=ROW(Queue)-ROW(INDEX(Queue,1,1))+1

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),1))

In G3 control+shift+enter and copy down:

=IF(ROWS($G$3:G3)>$G$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),Ivec),ROWS($G$3:G3))))

In H1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),1))

In h3 control+shift+enter and copy down:

=IF(ROWS(H$3:H3)>H$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(H$3:H3))))

In I1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),1))

In E3 control+shift+enter and copy down:

=IF(ROWS(I$3:I3)>I$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(I$3:I3))))

 

[Maddy1234]

Hi Aladin,

Thank you very much for your reply but I have something I don't completely understand. Can you please help me with "Ivec" and where does it go and where the code "=ROW(Queue)-ROW(INDEX(Queue,1,1))+1" should be put in?

Additionally just wanted to make it clear that non-prodcution : breaks will also contain "Sick Leave" and "Planned Leave".

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Queue	Start Time	End Time	Production	Volumes		4​	2​	2​
2​	Queue 1	19:00:00​	20:00:00​	P​	2​		production	non-production: no break	non-production: break
3​	Comfort Break	20:00:00​	20:10:00​	N​	-​		Queue 1	Team Meeting	Comfort Break
4​	Queue 2	20:10:00​	21:30:00​	P​	3​		Queue 2	Staff Reviews	Meal Break
5​	Team Meeting	21:30:00​	22:00:00​	N​	-​		Queue 3
6​	Queue 1	22:00:00​	22:15:00​	P​	1​		Queue 4
7​	Queue 3	22:15:00​	23:30:00​	P​	5​
8​	Staff Reviews	23:30:00​	0:10:00​	N​	-​
9​	Queue 4	0:10:00​	0:40:00​	P​	2​
10​	Meal Break	0:40:00​	1:30:00​	N​	-​
11​	Queue 2	1:30:00​	2:00:00​	P​	1​

<tbody>
</tbody>

Define the following as referring to respective ranges:

Queue
STime
ETime
Production
Volums

and

Ivec in the Name Manager as referring to:

=ROW(Queue)-ROW(INDEX(Queue,1,1))+1

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),1))

In G3 control+shift+enter and copy down:

=IF(ROWS($G$3:G3)>$G$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),Ivec),ROWS($G$3:G3))))

In H1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),1))

In h3 control+shift+enter and copy down:

=IF(ROWS(H$3:H3)>H$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(H$3:H3))))

In I1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),1))

In E3 control+shift+enter and copy down:

=IF(ROWS(I$3:I3)>I$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue)),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(I$3:I3))))

 

[Aladin Akyurek]

Hi Aladin,

Thank you very much for your reply but I have something I don't completely understand. Can you please help me with "Ivec" and where does it go and where the code "=ROW(Queue)-ROW(INDEX(Queue,1,1))+1" should be put in?

Additionally just wanted to make it clear that non-prodcution : breaks will also contain "Sick Leave" and "Planned Leave".

Select A2:A11 and name this this range Queue via the Name Box.
Select B2:B11 and name this this range STime via the Name Box.
Select C2:C11 and name this this range ETime via the Name Box.
Select D2:D11 and name this this range Production via the Name Box.
Select E2:E11 and name this this range Volumes via the Name Box.

Define Ivec in Formulas | Name Manager as referring to:

=ROW(Queue)-ROW(INDEX(Queue,1,1))+1

The additional request affects some of the formulas...

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),1))

In G3 control+shift+enter and copy down:

=IF(ROWS($G$3:G3)>$G$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="p",MATCH(Queue,Queue,0))),Ivec),Ivec),ROWS($G$3:G3))))

In H1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-(ISNUMBER(SEARCH("break",Queue))+ISNUMBER(SEARCH("leave",Queue))),MATCH(Queue,Queue,0)))),Ivec),1))

In H3 control+shift+enter, not just enter, and copy down:

=IF(ROWS(H$3:H3)>H$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(1-(ISNUMBER(SEARCH("break",Queue))+ISNUMBER(SEARCH("leave",Queue))),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(H$3:H3))))

In I1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue))+ISNUMBER(SEARCH("leave",Queue)),MATCH(Queue,Queue,0)))),Ivec),1))

In I3 control+shift+enter, not just enter, and copy down:

=IF(ROWS(I$3:I3)>I$1,"",INDEX(Queue,SMALL(IF(FREQUENCY(IF(1-(Queue=""),IF(Production="n",IF(ISNUMBER(SEARCH("break",Queue))+ISNUMBER(SEARCH("leave",Queue)),MATCH(Queue,Queue,0)))),Ivec),Ivec),ROWS(I$3:I3))))