Making a list of the row title under the corresponding column title in if a cell has X

Sorry for the confusing title, I couldn't think of a concise way to summarize what I want to do. I want to have an input table with the column titles being the title of something and the row names as the names of people. The idea is, when the cell contains something, probably an X, a separate table with the same column titles will populate a list of the names X'ed in the corresponding column. Below I have attempted to make some tables with what I imagine it looking like.

Input table where cells are X'ed as needed.

	1000.1A	2000.2B	3000.3C
Smith			X
Jones		X	X
Williams	X	X	X

Output table where the list is generated based on the X's

1000.1A	2000.2B	3000.3C
Williams	Jones	Jones
	Williams	Smith
		Williams

I am able to get the names as an output to the correct column, but I do not know how to move them up to the top-most open space. Right now I am using =IF(B2<>"",A1,"") to get the name in the appropriate list. But as you can see, it will leave an empty space for that row under the output column. This is where I get stuck trying to squish it up to the top.

Also, as a separate note, I would like to sort them by alphabetical order when they are in that column.

Any help is much appreciated.

With data on Sheet1
Create a Sheet2

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A1:$A3,AGGREGATE(15,6,ROW($A1:$A3)/((Sheet1!$B$1:$B$3="X")),ROWS(A$1:A1))-(1-1),1),"")

in Sheet2!B1
=IFERROR(INDEX(Sheet1!$B1:$B3,AGGREGATE(15,6,ROW($A1:$A3)/((Sheet1!$C$1:$C$3="X")),ROWS(A$1:A1))-(1-1),1),"")

in Sheet2!C1
=IFERROR(INDEX(Sheet1!$C1:$C3,AGGREGATE(15,6,ROW($A1:$A3)/((Sheet1!$D$1:$D$3="X")),ROWS(A$1:A1))-(1-1),1),"")

copy down as far as row 3

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

UPDATE: Oh you have! :-)

in Sheet2!A2
=IFERROR(INDEX(Sheet1!$A$2:$A$4,AGGREGATE(15,6,ROW($A2:$A4)/((Sheet1!B$2:B$4="X")),ROWS(A$1:A1))-(2-1),1),"")
copy across and down to C4

The general format of the formula

'=IFERROR(INDEX(range,AGGREGATE(15,6,ROW(range)/(range to check and condition),ROWS(first cell reference with anchored row - first row))-(first row-1),column),"")

Aah, my formula works but only if the data starts on row 1.
I'll have a look at changing it for other rows.

UPDATE: Ah, left out my $ signs.
This looks better

=IFERROR(INDEX(Sheet1!$A$2:$A$4,AGGREGATE(15,6,ROW($A$2:$A$4)/((Sheet1!B$2:B$4="X")),ROWS(A$1:A1))-(2-1),1),"")
copy across and down to C4

Try

A2

Formula:

Please Login or Register  to view this content.

copy across