Need help with a formula

[davidalade]

Hi everyone, I recently joined this group and I do not know if the question I want to ask, has been asked and answered in the past.
I will appreciate the help. Here is my question:

There is a list of projects in columns A2:A7. There is a list of KPIs in row B1:F1. In the table B2:F7 there is data that indicates whether the project has a High, Medium, or Low impact level on the KPIs. There are also blank cells for no impact level on the KPI.

List of Projects	KPI1​	KPI2​	KPI3​	KPI4​	KPI5​
Project 1	H​		M​	H​	L​
Project 2	M​	H​		M​	H​
Project 3	M​		H​	M​
Project 4		L​			M​
Project 5	H​		H​	H​
Project 6		L​		L​	H​

I need a formula that stacks all the projects in a single column, and on the second column the corresponding KPI, while on the third column the related impact level. (Removing the blank ones too). Just like I have shown below.

List of Projects	KPI​	Impact​
Project 1	KPI1​	H​
Project 1	KPI3​	M​
Project 1	KPI4​	H​
Project 1	KPI5​	L​
Project 2	KPI1​	M​
Project 2	KPI2​	H​
Project 2	KPI4​	M​
Project 3	KPI1​	M​
Project 3	KPI3​	H​
Project 4	KPI2​	L​
Project 4	KPI5​	M​
Project 5	KPI1​	H​
Project 5	KPI3​	H​
Project 5	KPI4​	H​
Project 6	KPI2​	L​
Project 6	KPI4​	L​
Project 6	KPI5​	H​

I will really appreciate the help from the Excel Kings in this group

 

[Fluff]

Hi & welcome to MrExcel..

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	List of Projects	KPI1	KPI2	KPI3	KPI4	KPI5
2	Project 1	H		M	H	L		Project 1	KPI1	H
3	Project 2	M	H		M	H		Project 1	KPI3	M
4	Project 3	M		H	M			Project 1	KPI4	H
5	Project 4		L			M		Project 1	KPI5	L
6	Project 5	H		H	H			Project 2	KPI1	M
7	Project 6		L		L	H		Project 2	KPI2	H
8								Project 2	KPI4	M
9								Project 2	KPI5	H
10								Project 3	KPI1	M
11								Project 3	KPI3	H
12								Project 3	KPI4	M
13								Project 4	KPI2	L
14								Project 4	KPI5	M
15								Project 5	KPI1	H
16								Project 5	KPI3	H
17								Project 5	KPI4	H
18								Project 6	KPI2	L
19								Project 6	KPI4	L
20								Project 6	KPI5	H
21
Sheet6

Cell Formulas
Range	Formula
H2:J20	H2	=HSTACK(TOCOL(IF(B2:F7<>"",A2:A7,1/0),2),TOCOL(IF(B2:F7<>"",B1:F1,1/0),2),TOCOL(B2:F7,1))
Dynamic array formulas.

 

[davidalade]

Hi & welcome to MrExcel..

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	List of Projects	KPI1	KPI2	KPI3	KPI4	KPI5
2	Project 1	H		M	H	L		Project 1	KPI1	H
3	Project 2	M	H		M	H		Project 1	KPI3	M
4	Project 3	M		H	M			Project 1	KPI4	H
5	Project 4		L			M		Project 1	KPI5	L
6	Project 5	H		H	H			Project 2	KPI1	M
7	Project 6		L		L	H		Project 2	KPI2	H
8								Project 2	KPI4	M
9								Project 2	KPI5	H
10								Project 3	KPI1	M
11								Project 3	KPI3	H
12								Project 3	KPI4	M
13								Project 4	KPI2	L
14								Project 4	KPI5	M
15								Project 5	KPI1	H
16								Project 5	KPI3	H
17								Project 5	KPI4	H
18								Project 6	KPI2	L
19								Project 6	KPI4	L
20								Project 6	KPI5	H
21
Sheet6

Cell Formulas
Range	Formula
H2:J20	H2	=HSTACK(TOCOL(IF(B2:F7<>"",A2:A7,1/0),2),TOCOL(IF(B2:F7<>"",B1:F1,1/0),2),TOCOL(B2:F7,1))
Dynamic array formulas.

It appears to work. Yay!!!! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.