Dynamic List Of Unique Items based on Multiple Criteria

M

Maddy1234

New Member
Joined
Mar 9, 2018
Messages
24
Hello experts,

I have the following the dataset.

[TABLE="width: 492"]
<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>[TR]
[TD="width: 111, bgcolor: #5B9BD5"]Queue[/TD]
[TD="width: 99, bgcolor: #5B9BD5"]Start Time[/TD]
[TD="width: 91, bgcolor: #5B9BD5"]End Time[/TD]
[TD="width: 103, bgcolor: #5B9BD5"]Production[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Volumes[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Queue 1[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]19:00:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]20:00:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Comfort Break[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]20:00:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]20:10:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]N[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]-[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Queue 2[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]20:10:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]21:30:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Team Meeting[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]21:30:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]22:00:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]N[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]-[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Queue 1[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]22:00:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]22:15:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Queue 3[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]22:15:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]23:30:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Staff Reviews[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]23:30:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0:10:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]N[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]-[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Queue 4[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0:10:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0:40:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Meal Break[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0:40:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1:30:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]N[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]-[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Queue 2[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]1:30:00[/TD]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]2:00:00[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]P[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

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

Result :

[TABLE="width: 103"]
<colgroup><col width="103" style="width: 77pt;"></colgroup><tbody>[TR]
[TD="width: 103"]Production[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Queue 1[/TD]
[/TR]
[TR]
[TD]Queue 2[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Queue 3[/TD]
[/TR]
[TR]
[TD]Queue 4[/TD]
[/TR]
</tbody>[/TABLE]

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 :

[TABLE="width: 135"]
<colgroup><col width="135" style="width: 101pt;"></colgroup><tbody>[TR]
[TD="width: 135, bgcolor: #70AD47"]Non Production[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]Comfort Break[/TD]
[/TR]
[TR]
[TD]Team Meeting[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]Staff Reviews[/TD]
[/TR]
[TR]
[TD]Meal Break[/TD]
[/TR]
</tbody>[/TABLE]

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 :

[TABLE="width: 334"]
<colgroup><col width="135" style="width: 101pt;"><col width="87" style="width: 65pt;"><col width="112" style="width: 84pt;"></colgroup><tbody>[TR]
[TD="width: 135, bgcolor: #70AD47"]Non Production[/TD]
[TD="width: 87"][/TD]
[TD="width: 112, bgcolor: #5B9BD5"]Breaks[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]Team Meeting[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Meal break[/TD]
[/TR]
[TR]
[TD]Staff Reviews[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Comfort Break[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#5B9BD5"]Queue[/td][td="bgcolor:#5B9BD5"]Start Time[/td][td="bgcolor:#5B9BD5"]End Time[/td][td="bgcolor:#5B9BD5"]Production[/td][td="bgcolor:#5B9BD5"]Volumes[/td][td][/td][td]
4​
[/td][td]
2​
[/td][td]
2​
[/td][/tr]
[tr][td]
2​
[/td][td]Queue 1[/td][td]
19:00:00​
[/td][td]
20:00:00​
[/td][td]
P​
[/td][td]
2​
[/td][td][/td][td="bgcolor:#FFC000"]production[/td][td="bgcolor:#FFC000"]non-production: no break[/td][td="bgcolor:#FFC000"]non-production: break[/td][/tr]
[tr][td]
3​
[/td][td]Comfort Break[/td][td]
20:00:00​
[/td][td]
20:10:00​
[/td][td]
N​
[/td][td]
-​
[/td][td][/td][td]Queue 1[/td][td]Team Meeting[/td][td]Comfort Break[/td][/tr]
[tr][td]
4​
[/td][td]Queue 2[/td][td]
20:10:00​
[/td][td]
21:30:00​
[/td][td]
P​
[/td][td]
3​
[/td][td][/td][td]Queue 2[/td][td]Staff Reviews[/td][td]Meal Break[/td][/tr]
[tr][td]
5​
[/td][td]Team Meeting[/td][td]
21:30:00​
[/td][td]
22:00:00​
[/td][td]
N​
[/td][td]
-​
[/td][td][/td][td]Queue 3[/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td]Queue 1[/td][td]
22:00:00​
[/td][td]
22:15:00​
[/td][td]
P​
[/td][td]
1​
[/td][td][/td][td]Queue 4[/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]Queue 3[/td][td]
22:15:00​
[/td][td]
23:30:00​
[/td][td]
P​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td]Staff Reviews[/td][td]
23:30:00​
[/td][td]
0:10:00​
[/td][td]
N​
[/td][td]
-​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td]Queue 4[/td][td]
0:10:00​
[/td][td]
0:40:00​
[/td][td]
P​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td]Meal Break[/td][td]
0:40:00​
[/td][td]
1:30:00​
[/td][td]
N​
[/td][td]
-​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td]Queue 2[/td][td]
1:30:00​
[/td][td]
2:00:00​
[/td][td]
P​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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))))
 
Upvote 0
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".

Aladin Akyurek said:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD="bgcolor: #5B9BD5"]Queue[/TD]
[TD="bgcolor: #5B9BD5"]Start Time[/TD]
[TD="bgcolor: #5B9BD5"]End Time[/TD]
[TD="bgcolor: #5B9BD5"]Production[/TD]
[TD="bgcolor: #5B9BD5"]Volumes[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Queue 1[/TD]
[TD]
19:00:00​
[/TD]
[TD]
20:00:00​
[/TD]
[TD]
P​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]production[/TD]
[TD="bgcolor: #FFC000"]non-production: no break[/TD]
[TD="bgcolor: #FFC000"]non-production: break[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Comfort Break[/TD]
[TD]
20:00:00​
[/TD]
[TD]
20:10:00​
[/TD]
[TD]
N​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]Queue 1[/TD]
[TD]Team Meeting[/TD]
[TD]Comfort Break[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Queue 2[/TD]
[TD]
20:10:00​
[/TD]
[TD]
21:30:00​
[/TD]
[TD]
P​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]Queue 2[/TD]
[TD]Staff Reviews[/TD]
[TD]Meal Break[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Team Meeting[/TD]
[TD]
21:30:00​
[/TD]
[TD]
22:00:00​
[/TD]
[TD]
N​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]Queue 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Queue 1[/TD]
[TD]
22:00:00​
[/TD]
[TD]
22:15:00​
[/TD]
[TD]
P​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]Queue 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Queue 3[/TD]
[TD]
22:15:00​
[/TD]
[TD]
23:30:00​
[/TD]
[TD]
P​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]Staff Reviews[/TD]
[TD]
23:30:00​
[/TD]
[TD]
0:10:00​
[/TD]
[TD]
N​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]Queue 4[/TD]
[TD]
0:10:00​
[/TD]
[TD]
0:40:00​
[/TD]
[TD]
P​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]Meal Break[/TD]
[TD]
0:40:00​
[/TD]
[TD]
1:30:00​
[/TD]
[TD]
N​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]Queue 2[/TD]
[TD]
1:30:00​
[/TD]
[TD]
2:00:00​
[/TD]
[TD]
P​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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))))
Click to expand...
 
Upvote 0
Maddy1234 said:
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".
Click to expand...

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))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
605
Peter_SSs
Peter_SSs
C
  • Question Question
Re: Number Selection - Extension
Replies
7
Views
955
cotech_10
C

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

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