Formula Required: Sort/Order output data of two columns into one

[longbow2000]

Good Day

I need to order a previous process outcome into a new arrival number, as follows:

• Customer receives a number and proceed to the Application Window. Two processing counters for assistance. Processing time is not the same, as applications are not the same.
• The end time of the customer is recorded per processing counter.
• Thereafter, the processed customer moves to the Payment Window.
• In the attached screenshot, customer 1 remains first served, first processed and first to move to the Payment Window.
• However, initial customer 2 processing time is longer and becomes customer 8 at the Payment Window.

In the screenshot (image attached) the values are manually entered into Column E; however, will you be able to assist in a formula in Column E to automate the process? The mini-sheet is also attached

Thanks in advance.
Longbow2000

Border_Optimisation.xlsx
	A	B	C	D	E	F
1		Step 1: Application Window			Step 2: Payment Window
2	Customer	Teller 1	Teller 2		New Customer	Arrival
3	#	End Time	End Time		#	Time
4	1		32		1
5	2	87			2
6	3		39		3
7	4		48		4
8	5		52		5
9	6		64		6
10	7		81		7
11	8		86		8
12	9		88		9
13	10	97			10
14	11		107		11
15	12	133			12
16	13		154		13
17	14	163			14
18	15		175		15
19	16	186			16
20	17		215		17
21	18	221			18
22	19		244		19
23	20	240			20
24	21	241			21
25	22	247			22
26	23		248		23
27	24	259			24
28	25		260		25
End_Teller_time

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[longbow2000]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, updated the details. Using Office 365

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F
1		Step 1: Application Window			Step 2: Payment Window
2	Customer	Teller 1	Teller 2		New Customer	Arrival
3	#	End Time	End Time		#	Time
4	1		32		1	32
5	2	87			2	39
6	3		39		3	48
7	4		48		4	52
8	5		52		5	64
9	6		64		6	81
10	7		81		7	86
11	8		86		8	87
12	9		88		9	88
13	10	97			10	97
14	11		107		11	107
15	12	133			12	133
16	13		154		13	154
17	14	163			14	163
18	15		175		15	175
19	16	186			16	186
20	17		215		17	215
21	18	221			18	221
22	19		244		19	240
23	20	240			20	241
24	21	241			21	244
25	22	247			22	247
26	23		248		23	248
27	24	259			24	259
28	25		260		25	260
Data

Cell Formulas
Range	Formula
F4:F28	F4	=SORT(TOCOL(B4:C28,1))
Dynamic array formulas.

 

[kvsrinivasamurthy]

Do you require the same result as shown in E column.. It is just a serial order numbers.

 

[longbow2000]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F
1		Step 1: Application Window			Step 2: Payment Window
2	Customer	Teller 1	Teller 2		New Customer	Arrival
3	#	End Time	End Time		#	Time
4	1		32		1	32
5	2	87			2	39
6	3		39		3	48
7	4		48		4	52
8	5		52		5	64
9	6		64		6	81
10	7		81		7	86
11	8		86		8	87
12	9		88		9	88
13	10	97			10	97
14	11		107		11	107
15	12	133			12	133
16	13		154		13	154
17	14	163			14	163
18	15		175		15	175
19	16	186			16	186
20	17		215		17	215
21	18	221			18	221
22	19		244		19	240
23	20	240			20	241
24	21	241			21	244
25	22	247			22	247
26	23		248		23	248
27	24	259			24	259
28	25		260		25	260
Data

Cell Formulas
Range	Formula
F4:F28	F4	=SORT(TOCOL(B4:C28,1))
Dynamic array formulas.

Hi, this is just what I require; however, I get a #NAME? error with the "TOCOL" part. Viewing the Microsoft Account detail, I have Microsoft 365 Apps for enterprise, Version 2202 (Build 1493 .20858)

 

[Fluff]

Ok, you don't have the latest update yet, how about

Fluff.xlsm
	A	B	C	D	E	F
1		Step 1: Application Window			Step 2: Payment Window
2	Customer	Teller 1	Teller 2		New Customer	Arrival
3	#	End Time	End Time		#	Time
4	1		32		1	32
5	2	87			2	39
6	3		39		3	48
7	4		48		4	52
8	5		52		5	64
9	6		64		6	81
10	7		81		7	86
11	8		86		8	87
12	9		88		9	88
13	10	97			10	97
14	11		107		11	107
15	12	133			12	133
16	13		154		13	154
17	14	163			14	163
18	15		175		15	175
19	16	186			16	186
20	17		215		17	215
21	18	221			18	221
22	19		244		19	240
23	20	240			20	241
24	21	241			21	244
25	22	247			22	247
26	23		248		23	248
27	24	259			24	259
28	25		260		25	260
Data

Cell Formulas
Range	Formula
F4:F28	F4	=LET(a,B4:C28,r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),x,INDEX(a,MOD(s,r)+1,INT(s/r)+1),SORT(FILTER(x,x<>"")))
Dynamic array formulas.

 

[longbow2000]

Ok, you don't have the latest update yet, how about

Fluff.xlsm
	A	B	C	D	E	F
1		Step 1: Application Window			Step 2: Payment Window
2	Customer	Teller 1	Teller 2		New Customer	Arrival
3	#	End Time	End Time		#	Time
4	1		32		1	32
5	2	87			2	39
6	3		39		3	48
7	4		48		4	52
8	5		52		5	64
9	6		64		6	81
10	7		81		7	86
11	8		86		8	87
12	9		88		9	88
13	10	97			10	97
14	11		107		11	107
15	12	133			12	133
16	13		154		13	154
17	14	163			14	163
18	15		175		15	175
19	16	186			16	186
20	17		215		17	215
21	18	221			18	221
22	19		244		19	240
23	20	240			20	241
24	21	241			21	244
25	22	247			22	247
26	23		248		23	248
27	24	259			24	259
28	25		260		25	260
Data

Cell Formulas
Range	Formula
F4:F28	F4	=LET(a,B4:C28,r,ROWS(a),s,SEQUENCE(r*COLUMNS(a),,0),x,INDEX(a,MOD(s,r)+1,INT(s/r)+1),SORT(FILTER(x,x<>"")))
Dynamic array formulas.

100% Great! Thank you and greetings from South Africa, looking forward to the next rugby game

 

[Fluff]

You're welcome & thanks for the feedback.
With luck we'll next play each other on 28th Oct. (but i doubt we'll make it)