Creating a List from Table Data

[jpelletierfl]

Hello,

I am looking to see if there is a way, wether it be through FBA or functions to find only the cells that have values in a table and return the row and column header in a list form ignoring all blanks.

Below is an example of the table and list I am looking to have it return
Each person can have a value in multiple columns but I do not want to return blank cells.

Is this possible?

 

[jpelletierfl]

	SAIL	PARK	ROSE	TRAIL	MOUNT	CHURCH	MAX	REGAL	PIT
	Shop	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	Job 7	Job 8
Al		1			3		4
Chris				8
Jeff						8
Mike			8
Paula	8
Ricky						2			6
Steve						2		6
Tammy				2		4			4
Violet		4	3					1
Al	Job 1	1​	PARK
Al	Job 4	3​	MOUNT
Al	Job 6	4​	MAX
Chris	Job 3	8​	TRAIL
Jeff	Job 5	8​	CHURCH
Mike	Job 2	8​	ROSE
Paula	Shop	8​	SAIL
Ricky	Job 5	2​	CHURCH
Ricky	Job 8	6​	PIT
Steve	Job 5	2​	CHURCH
Steve	Job 7	6​	REGAL
Tammy	Job 3	2​	TRAIL
Tammy	Job 5	4​	CHURCH
Tammy	Job 8	4​	PIT
Violet	Job 1	4​	PARK
Violet	Job 2	3​	ROSE
Violet	Job 7	1​	REGAL

 

[Fluff]

Thanks for that.
How about

=LET(Ra,A3:A11,Rb,B2:J2,Rc,B3:J11,Rd,B1:J1,c,COLUMNS(Rc),s,SEQUENCE(ROWS(Ra)*c,,0),xa,INDEX(Ra,INT(s/c)+1),xb,INDEX(Rb,MOD(s,c)+1),xc,INDEX(Rc,INT(s/c)+1,MOD(s,c)+1),xd,INDEX(Rd,MOD(s,c)+1),FILTER(CHOOSE({1,2,3,4},xa,xb,xc,xd),xc<>""))

or if you have now got the new functions

=HSTACK(TOCOL(IF(B3:J11="",x,A3:A11),2),TOCOL(IF(B3:J11="",x,B2:J2),2),TOCOL(B3:J11,1),TOCOL(IF(B3:J11="",x,B1:J1),2))

 

[alansidman]

Here is an alternative solution with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{":", "Name"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

Book7
	A	B	C	D	E	F	G	H	I	J
2	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
3		SAIL	PARK	ROSE	TRAIL	MOUNT	CHURCH	MAX	REGAL	PIT
4		Shop	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	Job 7	Job 8
5	Al		1			3		4
6	Chris				8
7	Jeff						8
8	Mike			8
9	Paula	8
10	Ricky						2			6
11	Steve						2		6
12	Tammy				2		4			4
13	Violet		4	3					1
14
15	Name	Attribute.1	Attribute.2	Value
16	Al	PARK	Job 1	1
17	Al	MOUNT	Job 4	3
18	Al	MAX	Job 6	4
19	Chris	TRAIL	Job 3	8
20	Jeff	CHURCH	Job 5	8
21	Mike	ROSE	Job 2	8
22	Paula	SAIL	Shop	8
23	Ricky	CHURCH	Job 5	2
24	Ricky	PIT	Job 8	6
25	Steve	CHURCH	Job 5	2
26	Steve	REGAL	Job 7	6
27	Tammy	TRAIL	Job 3	2
28	Tammy	CHURCH	Job 5	4
29	Tammy	PIT	Job 8	4
30	Violet	PARK	Job 1	4
31	Violet	ROSE	Job 2	3
32	Violet	REGAL	Job 7	1
Sheet1

 

[jpelletierfl]

Thanks for that.
How about

=LET(Ra,A3:A11,Rb,B2:J2,Rc,B3:J11,Rd,B1:J1,c,COLUMNS(Rc),s,SEQUENCE(ROWS(Ra)*c,,0),xa,INDEX(Ra,INT(s/c)+1),xb,INDEX(Rb,MOD(s,c)+1),xc,INDEX(Rc,INT(s/c)+1,MOD(s,c)+1),xd,INDEX(Rd,MOD(s,c)+1),FILTER(CHOOSE({1,2,3,4},xa,xb,xc,xd),xc<>""))

or if you have now got the new functions

=HSTACK(TOCOL(IF(B3:J11="",x,A3:A11),2),TOCOL(IF(B3:J11="",x,B2:J2),2),TOCOL(B3:J11,1),TOCOL(IF(B3:J11="",x,B1:J1),2))

Thank You!

 

[jpelletierfl]

Here is an alternative solution with Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{":", "Name"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

Book7
	A	B	C	D	E	F	G	H	I	J
2	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
3		SAIL	PARK	ROSE	TRAIL	MOUNT	CHURCH	MAX	REGAL	PIT
4		Shop	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	Job 7	Job 8
5	Al		1			3		4
6	Chris				8
7	Jeff						8
8	Mike			8
9	Paula	8
10	Ricky						2			6
11	Steve						2		6
12	Tammy				2		4			4
13	Violet		4	3					1
14
15	Name	Attribute.1	Attribute.2	Value
16	Al	PARK	Job 1	1
17	Al	MOUNT	Job 4	3
18	Al	MAX	Job 6	4
19	Chris	TRAIL	Job 3	8
20	Jeff	CHURCH	Job 5	8
21	Mike	ROSE	Job 2	8
22	Paula	SAIL	Shop	8
23	Ricky	CHURCH	Job 5	2
24	Ricky	PIT	Job 8	6
25	Steve	CHURCH	Job 5	2
26	Steve	REGAL	Job 7	6
27	Tammy	TRAIL	Job 3	2
28	Tammy	CHURCH	Job 5	4
29	Tammy	PIT	Job 8	4
30	Violet	PARK	Job 1	4
31	Violet	ROSE	Job 2	3
32	Violet	REGAL	Job 7	1
Sheet1

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.