List of unique names from from different columns

This is kind of kludgy, but it works. I made 4 separate fixed named ranges called Masters, US_Open, etc. each extending from row 2 to 12 because I didn't feel like extending all of them for 49 rows. Then in Column L, I highlighted rows 2 to 12 and entered in an array formula =Masters. The next 11 rows is another array formula = US_OPEN, etc.

I wanted to identify blanks, bit ISBLANK didn't work, probably because the source files contain formulas. Likewise LEN(Ln) didn't evaluate to zero for what looked like the blank cells. So I checked LEN(Ln) > 1 and that worked. So we will have to assume that all the players have names with two or more letters.

I used these consecutive, "stacked on top of one another," array formulas as the source data for a pivot table. This table, that contains a unique list of names, is on the pivot sheet.

It would probably be cleaner to do this with VBA.

For some reason, the board is double posting my submissions today.

Try this...

Create these dynamic named ranges.

Name: PGA
Refers to:

=OFFSET('Record Scores Here'!$G$2,,,COUNTIF('Record Scores Here'!$G$2:$G$100,"?*"))

Name: British
Refers to:

=OFFSET('Record Scores Here'!$E$2,,,COUNTIF('Record Scores Here'!$E$2:$E$100,"?*"))

Name: Open
Refers to:

=OFFSET('Record Scores Here'!$C$2,,,COUNTIF('Record Scores Here'!$C$2:$C$100,"?*"))

Name: Masters
Refers to:

=OFFSET('Record Scores Here'!$A$2,,,COUNTIF('Record Scores Here'!$A$2:$A$100,"?*"))

Then, enter this formula in J2:

=LOOKUP("zzz",CHOOSE({1,2,3,4,5},"",INDEX(PGA,MATCH(0,INDEX(COUNTIF(J$1:J1,PGA),0),0)),INDEX(British,MATCH(0,INDEX(COUNTIF(J$1:J1,British),0),0)),INDEX(Open,MATCH(0,INDEX(COUNTIF(J$1:J1,Open),0),0)),INDEX(Masters,MATCH(0,INDEX(COUNTIF(J$1:J1,Masters),0),0))))

Copy down until you get blanks.