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]

See post#9

 

[Chandler8]

Sorry didn't see that! Thank you. So I tried the formula on my computer with Excel 365 but I changed the A2:E10 to F4:V1000 and its giving me a spill error.

=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<>"")))

 

[Fluff]

In that case you need to clear all the cells below where you put the formula.

 

[Chandler8]

I'll let you know if it works as soon as I get access to my 365 Excel Computer. Still waiting on it. Thank you!

 

[Fluff]

Ok

 

[Chandler8]

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

Was finally able to test this formula on the 365 computer. It seems to work somewhat, but it looks like it grabs only the Registered_Guest1 column. Instead of all names. For the unfilled names it gives me (#CALC) error.

 

[Fluff]

That will get the data from all the columns & put it in one vertical list.

 

[Chandler8]

Okay, I will keep trying. Thanks

 

[Fluff]

What did you mean by

For the unfilled names it gives me (#CALC) error.

 

[Chandler8]

What did you mean by

Out of the 600+ names it only pulls from the first column which is like 100 and then gives me #calc errors for the other 500 going down the list.