Creating Single Column list with this data range

[keef2]

Hey All!

Got one that I'm not sure the best way to handle. I will have a data set such as the following which changes daily and as of now user can input Employee names in range C6:I9. I am hoping to get some guidance or help on taking this range and generating a single column list from it while ignoring blanks. Bonus points to be sorted in alphabetical order but not necessary. Any help is always appreciated, thanks!

Daily Schedule Template Wed 9-21-22 Prototype.xlsm
	A	B	C	D	E	F	G	H	I
3	Job #:		Job Name:					Time:	7:00
4			Location:
5	Foreman:		Bob Miller			# Deliveries:
6	Employees :		Robert Crowe		Paul Peters		Harper Smith
7			Dan Smith		Bat Man		Dylon Herrera
8			Jake Miles		Buddy Guy		Tony Day
9
Schedule

Cell Formulas
Range	Formula
D3	D3	=IFERROR(VLOOKUP(B3,Table2,2,FALSE),"")
D4	D4	=IFERROR(VLOOKUP(B3,Table2,3,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C31:D32,C3:D4,C10:D11,C17:D18	Expression	=COUNTIF($C3,"*Job Name*")	text	NO

Side note: there will be 6 to 7 of these lists so please keep that in mind if possible.

 

[keef2]

I got this figured out with @Fluff previous post Here is the solution:

=UNIQUE(LET( ranges, (Schedule!C6:C9,Schedule!D6:D9,Schedule!E6:E9,Schedule!F6:F9,Schedule!G6:G9,Schedule!H6:H9,Schedule!I6:I9), areas, AREAS(ranges), rows, ROWS(Schedule!C6:C9), TotalRows, SEQUENCE(areas*rows), NumArea, CEILING.MATH(SEQUENCE(areas*rows),rows)/rows, Row, TotalRows-(NumArea-1)*rows, indx,INDEX(ranges,Row,1,NumArea),FILTER(indx,indx<>"")))