Without using a pivot table, how can I transpose headers into one column where there is data in the cell for that row

[NRogalska]

List of data col A with different values input into the appropriate col C-F. I want to have B reference the column header the value is in.

So in this example rows A2-A8 are my line items where their values are recorded in the appropriate product column, and I want that product column header to be shown in column B next to the appropriate line item: here Item A3 has a value recorded in col with header D1 so 'D1' appears in the cell next to item A3.

Item	Product	C1	D1	E1	F1	G1	H1
A2	F1				2​
A3	D1		3​
A4	G1					4​
A5	C1	5​
A6	E1			6​
A7	D1		7​
A8	H1						8​

I know I can do this manually by filtering each column excluding blank cells, and then copy/paste the col header into the remaining cells in col B, but this is quite laborious and I'd like a formula to do it so it happens everytime a new line/row is added with data.
I know I could use a very long 'if' or 'ifs' formula but hoping for something simpler.

 

[duggie33]

Give this a try...

Book1
	A	B	C	D	E	F	G	H
27	Item	Product	C1	D1	E1	F1	G1	H1
28	A2	F1				2
29	A3	D1		3
30	A4	G1					4
31	A5	C1	5
32	A6	E1			6
33	A7	D1		7
34	A8	H1						8
Sheet1

Cell Formulas
Range	Formula
B28:B34	B28	=FILTER($C$27:$H$27,C28:H28<>"","error")

Hope that helps,

Doug

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Item	Product	C1	D1	E1	F1	G1	H1
2	A2	F1				2
3	A3	D1		3
4	A4	G1					4
5	A5	C1	5
6	A6	E1			6
7	A7	D1		7
8	A8	H1						8
Master

Cell Formulas
Range	Formula
B2:B8	B2	=LOOKUP(2,1/(C2:H2<>""),$C$1:$H$1)