Need help with a formula

davidalade

New Member
Joined
Feb 12, 2024
Messages
2
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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 :)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi & welcome to MrExcel..

How about
Fluff.xlsm
ABCDEFGHIJ
1List of ProjectsKPI1KPI2KPI3KPI4KPI5
2Project 1HMHLProject 1KPI1H
3Project 2MHMHProject 1KPI3M
4Project 3MHMProject 1KPI4H
5Project 4LMProject 1KPI5L
6Project 5HHHProject 2KPI1M
7Project 6LLHProject 2KPI2H
8Project 2KPI4M
9Project 2KPI5H
10Project 3KPI1M
11Project 3KPI3H
12Project 3KPI4M
13Project 4KPI2L
14Project 4KPI5M
15Project 5KPI1H
16Project 5KPI3H
17Project 5KPI4H
18Project 6KPI2L
19Project 6KPI4L
20Project 6KPI5H
21
Sheet6
Cell Formulas
RangeFormula
H2:J20H2=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.
 
Upvote 1
Solution
Hi & welcome to MrExcel..

How about
Fluff.xlsm
ABCDEFGHIJ
1List of ProjectsKPI1KPI2KPI3KPI4KPI5
2Project 1HMHLProject 1KPI1H
3Project 2MHMHProject 1KPI3M
4Project 3MHMProject 1KPI4H
5Project 4LMProject 1KPI5L
6Project 5HHHProject 2KPI1M
7Project 6LLHProject 2KPI2H
8Project 2KPI4M
9Project 2KPI5H
10Project 3KPI1M
11Project 3KPI3H
12Project 3KPI4M
13Project 4KPI2L
14Project 4KPI5M
15Project 5KPI1H
16Project 5KPI3H
17Project 5KPI4H
18Project 6KPI2L
19Project 6KPI4L
20Project 6KPI5H
21
Sheet6
Cell Formulas
RangeFormula
H2:J20H2=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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top