Function for turning cells into a list

[Chandler8]

Hello all, so I need a formula to grab all of the peoples names in random cells from one sheet into another sheet while creating a straight down list of the names! And, if there is a way to (prompt/warning) when there is a duplicate name while not posting it to the list that would be very helpful! I have listed some pictures for examples below. Thank you very much!

Ex..xlsx
	A	B	C	D	E
1	Random_Guest 1	Random_Guest 2	Random_Guest 3	Random_Guest 4	Random_Guest 5
2	Billy Bob	Kellie Bob	Bradyn Bob
3	Kayla Ming	Joe Smith
4	Bobby Doe	Joey Lawrence	Larry Bill	John Corley	Kelc Corley
5	Ashton Piercing
6	Ashur Low	Tim Low
7	Lauv Smiley
8	Lola Jean	Michael Jean
9	Kayla Pearson	Michelle Pearson
10	David Kurt	Lindsey Kurt	Kiley Simb	Larry Simb
Names

Ex..xlsx
	A
1	Guest_List_Names
2	Formula Here/to generate the names with ability to drag down (one name per cell)
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
List

 

[Fluff]

How many cells will you have on the Names sheet?

 

[Chandler8]

It will be a continuous list as we are adding new people to the list every day. Right now we have about 600+ names.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Random_Guest 1	Random_Guest 2	Random_Guest 3	Random_Guest 4	Random_Guest 5		Unique List
2	Billy Bob	Kellie Bob	Bradyn Bob				Billy Bob
3	Kayla Ming	Joe Smith					Kellie Bob
4	Bobby Doe	Joey Lawrence	Larry Bill	John Corley	Kelc Corley		Bradyn Bob
5	Ashton Piercing						Kayla Ming
6	Ashur Low	Tim Low					Joe Smith
7	Lauv Smiley						Bobby Doe
8	Lola Jean	Michael Jean					Joey Lawrence
9	Kayla Pearson	Michelle Pearson					Larry Bill
10	David Kurt	Lindsey Kurt	Kiley Simb	Larry Simb			John Corley
11							Kelc Corley
12							Ashton Piercing
13							Ashur Low
14							Tim Low
15							Lauv Smiley
16							Lola Jean
17							Michael Jean
18							Kayla Pearson
19							Michelle Pearson
20							David Kurt
21							Lindsey Kurt
22							Kiley Simb
23							Larry Simb
Main

Cell Formulas
Range	Formula
G2:G23	G2	=UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(A2:E10<>"",A2:E10,""))&"</m></k>","//m"))
Dynamic array formulas.

 

[Chandler8]

That's exactly what I am looking for, but it's not working when I put it into my own sheet. Here is the real work. If you could show which exact cells that formula works with. The list starts in another sheet in A4 called (Guest_List).

Rental Arrivals.xlsm
	F	G	H	I	J
3	Registered_Guest1	Registered_Guest2	Registered_Guest3	Registered_Guest4	Registered_Guest5
4	Catherine Frye	Rick Jones
5	Greg Proctor	Trina Proctor	Mark Poovey	Sharon Poovey
6	Neal Blinde	Jessica Blinde	Arden Blinde	Norah Blinde	Graham Blinde
7	Stacie Menichella	Dan Menichella	Bryce Menichella	Brandon Artiqua	Lauren Artiqua
8	Dave Powell	Meredith Powell	Porter Powell	Rivers Powell	Ella Powell
9	David Taylor	Elizabeth Taylor	Broughton Taylor	William Taylor
10	Michael Patton	Amy Patton
11	Anne Warren	Ellie Waren	Timothy Warren	Claire Warren	Jeff Warren
12	Patrick Walsh	Emily Walsh	Michael Walsh	Martha Walsh	Patrick Jr. Walsh
13	Mark Frisch
14	Cam Findlay	Amy Findlay	David Findlay	Susan Findlay	Kathryn Beiser
15	Hans Stig Moller	Nancy Stig Moller	Catherine Stig Moller	Rita Stig Moller	Jens Stig Moller
16	Ginny Shaw	Ruthie Shaw	Finley Shaw	Robbie Shaw	Alan Shaw
17	Chip Dotson	Mike Kavanagh	John Lady
18	John Simmons	Cathy Simons	Elliott Simmons	David Simmons	Mary Catherine Simmons
19	Kelly Taylow	Matt Taylor	Hank Taylor	Maggie Taylor
20	Cathy Blackwelder	Emery Blackwelder	Chip Blackwelder	Alli Wengert	Ben Wengert
Guest_Info

Rental Arrivals.xlsm
	A
3	Guest_Name
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Guest_List

 

[Fluff]

What is the formula you used?

 

[Chandler8]

So the range will actually be starting with F4 and going through V:1000 as there are some lines with 15 guests long.
I put this formula in A4 in (Guest_List)
=UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(F4:V1000<>"",F4:V1000,""))&"</m></k>","//m"))

 

[Fluff]

So the range will actually be starting with F4 and going through V:1000

Which is why I asked how many names & you said about 600 that is over 10,000 cells & so the formula won't work.

 

[Fluff]

Try

=LET(Data,Guest_Info!A2:E10,r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),x,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),UNIQUE(FILTER(x,x<>"")))

 

[Chandler8]

As of right now it only goes from F4:V138 which add up to around 637 names. (Not counting the blanks). but I just wanted it to go to V:1000 for all the added names we do on this sheet in the coming years. My apologies.