Restructure list form from horizontal to vertical based on condition

[ExcelNewbie2020]

I dont know if this is possible using excel formula..

so i have a long range of column with data. see below example.. the idea is i want to rearrange the list to vertical but to non empty cell only (under issued/expired).. for example name 1 it got non empty cell (with x) under apple, cherry and got.. from that it needs to rearrange the list vertically including the names. Please see expected result at cell A16... thank you

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				apple		banana		cherry		berry		orange		dog		cat
2	name	dept.	postion	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired
3	Name1	data	data	X	X			X	X							X	X
4	Name2	data	data			X	X					X	X
5	Name3	data	data
6	Name4	data	data	X	X			X	X			X	X			X	X
7	Name5	data	data			X	X
8	Name6	data	data	X	X	X	X			X	X			X	X
9
10				where X can be text or dates
11
12
13
14	EXPECTED RESULT
15
16	Name1	apple
17	Name1	cherry
18	Name1	cat
19	Name2	banana
20	Name2	orange
21	Name4	apple
22	Name4	cherry
23	Name4	orange
24	Name4	cat
25	Name5	banana
26	Name6	apple
27	Name6	banana
28	Name6	berry
29	Name6	dog
Sheet3

 

[Peter_SSs]

Like this?

24 10 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				apple		banana		cherry		berry		orange		dog		cat
2	name	dept.	postion	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired
3	Name1	data	data	15/10/2024	X			X	X							X	X
4	Name2	data	data			8/08/2024	X					X	X
5	Name3	data	data
6	Name4	data	data	X	X			abcd	X			X	X			X	X
7	Name5	data	data			X	X
8	Name6	data	data	X	X	X	X			X	X			X	X
9
10
11
12
13
14	RESULT
15
16	Name1	apple
17	Name1	cherry
18	Name1	cat
19	Name2	banana
20	Name2	orange
21	Name4	apple
22	Name4	cherry
23	Name4	orange
24	Name4	cat
25	Name5	banana
26	Name6	apple
27	Name6	banana
28	Name6	berry
29	Name6	dog
To vertical

Cell Formulas
Range	Formula
A16:B29	A16	=TEXTSPLIT(TEXTJOIN("|",1,IF(D3:Q8<>"",IF(D2:Q2="issued",A3:A8&";"&D1:Q1,""),"")),";","|")
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				apple		banana		cherry		berry		orange		dog		cat
2	name	dept.	postion	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired	issued	expired
3	Name1	data	data	X	X			X	X							X	X
4	Name2	data	data			X	X					X	X
5	Name3	data	data
6	Name4	data	data	X	X			X	X			X	X			X	X
7	Name5	data	data			X	X
8	Name6	data	data	X	X	X	X			X	X			X	X
9
10
11
12
13
14
15
16	Name1	apple
17	Name1	cherry
18	Name1	cat
19	Name2	banana
20	Name2	orange
21	Name4	apple
22	Name4	cherry
23	Name4	orange
24	Name4	cat
25	Name5	banana
26	Name6	apple
27	Name6	banana
28	Name6	berry
29	Name6	dog
30
Main

Cell Formulas
Range	Formula
A16:B29	A16	=HSTACK(TOCOL(IF((D3:Q8<>"")*(D1:Q1<>""),A3:A8,1/0),2),TOCOL(IF((D3:Q8<>"")*(D1:Q1<>""),D1:Q1,1/0),2))
Dynamic array formulas.