Basic Impact-Effort Matrix

[mitzihowe]

Hello,

I am looking to build a simple Impact-Effort Matrix graph. After extracting some survey data, I run a macro to populate a table as such:

	High Impact	Low Impact	High Effort	Low Effort
Project 1	5	3	8	0
Project 2	5	3	6	2
Project 3	4	4	0	8
Project 4	2	6	3	5
Project 5	3	5	8	0
Project 6	3	5	4	4
Project 7	4	4	3	5
Project 8	4	4	4	4
Project 9	5	3	5	3
Project 10	4	4	4	4

This is based on a sample size of 8 voters who rate each project on whether a project is high/low in terms of effort and impact.

A project is rated High Impact (or whichever) as long as >50% votes are received. Hence, Project 1 is rated as High Impact and High Effort.

I want to be able to represent this data into a quadrant that just simply shows the project name in the quadrant it is supposed to be in. Something like this (the axes would be effort and impact:

	Project 1 Project 2 Project 9
Project 4	Project 5

Is there some way to do it? Apologies for the longish query and thanks in advance for any advise.

 

[Taul]

Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
	A	B	C	D	E	F	G	H	I
1		High Impact	Low Impact	High Effort	Low Effort		Impact	Effort	Quadrant
2	Project 1	5	3	8	0		High	High	A
3	Project 2	5	3	6	2		High	High	A
4	Project 3	4	4	0	8			Low
5	Project 4	2	6	3	5		Low	Low	D
6	Project 5	3	5	8	0		Low	High	C
7	Project 6	3	5	4	4		Low
8	Project 7	4	4	3	5			Low
9	Project 8	4	4	4	4
10	Project 9	5	3	5	3		High	High	A
11	Project 10	4	4	4	4
12
13
14		High effort	Low Effort			High Effort	Low Effort
15	High Impact	A	B		High Impact	Project 1 Project 2 Project 9
16	Low Impact	C	D		Low Impact	Project 5	Project 4
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11	H2	=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11	I2	=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16	F15	=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Edit - Cell F15 should have "wrap text" format

 

[mitzihowe]

Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
	A	B	C	D	E	F	G	H	I
1		High Impact	Low Impact	High Effort	Low Effort		Impact	Effort	Quadrant
2	Project 1	5	3	8	0		High	High	A
3	Project 2	5	3	6	2		High	High	A
4	Project 3	4	4	0	8			Low
5	Project 4	2	6	3	5		Low	Low	D
6	Project 5	3	5	8	0		Low	High	C
7	Project 6	3	5	4	4		Low
8	Project 7	4	4	3	5			Low
9	Project 8	4	4	4	4
10	Project 9	5	3	5	3		High	High	A
11	Project 10	4	4	4	4
12
13
14		High effort	Low Effort			High Effort	Low Effort
15	High Impact	A	B		High Impact	Project 1 Project 2 Project 9
16	Low Impact	C	D		Low Impact	Project 5	Project 4
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11	H2	=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11	I2	=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16	F15	=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Edit - Cell F15 should have "wrap text" format

Thanks so much, Taul!

Unfortunately, after entering the array formula I get a #NAME? error...

 

[mitzihowe]

Hi

Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
	A	B	C	D	E	F	G	H	I
1		High Impact	Low Impact	High Effort	Low Effort		Impact	Effort	Quadrant
2	Project 1	5	3	8	0		High	High	A
3	Project 2	5	3	6	2		High	High	A
4	Project 3	4	4	0	8			Low
5	Project 4	2	6	3	5		Low	Low	D
6	Project 5	3	5	8	0		Low	High	C
7	Project 6	3	5	4	4		Low
8	Project 7	4	4	3	5			Low
9	Project 8	4	4	4	4
10	Project 9	5	3	5	3		High	High	A
11	Project 10	4	4	4	4
12
13
14		High effort	Low Effort			High Effort	Low Effort
15	High Impact	A	B		High Impact	Project 1 Project 2 Project 9
16	Low Impact	C	D		Low Impact	Project 5	Project 4
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11	H2	=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11	I2	=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16	F15	=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Edit - Cell F15 should have "wrap text" format

Hi Taul,

I just realised it is because I'm using Excel 2016 and it has no TEXTJOIN function. Gah!

Really appreciate your help on this. If I just had that function life will be perfect...

 

[Taul]

Hi,
My apologies, I mistakenly thought TEXTJOIN existed in the 2016 version, my bad. I actually did a google search to check and I mis-read it.

As an alternative, there is a UDF (User Defined Function) available. Rick Rothstein has kindly produced one, the details are here Listed in post #2: -
Vlookup multiple Values then Concatenate all values in One cell

Also here at Ricks Rothstein’s Corner
LookUp Value and Concatenate All Found Results

I have used the UDF from his first post but I would suggest you read through the full post to see the discussion and how it has developed.
There are two Named Ranges:- SearchRange and ReturnRange, I have indicated this as a blue header over columns “A” and “I”
The formula in cell F15 is

 =LookUpConcat(B15,SearchRange,ReturnRange)

The search item is the letter “A” in cell B15

If you wish, you can hard code the Quadrant letter “A” in the formulas like this

 =LookUpConcat(“A”,SearchRange,ReturnRange)

I have ignored the optional parameters but you can include them if you wish.
The VBA code needs to go into a standard module.
The VBA code is:-

I have added & Chr(10) to Rick's code (in red text below) this is to wrap the test.
ReturnVal = ReturnRange(X).Value & Chr(10)

Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value & Chr(10)
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
  
End Function

Forum - Graph projects.xlsx
	A	B	C	D	E	F	G	H	I
1	ReturnRange	High Impact	Low Impact	High Effort	Low Effort		Impact	Effort	SearchRange
2	Project 1	5	3	8	0		High	High	A
3	Project 2	5	3	6	2		High	High	A
4	Project 3	4	4	0	8			Low
5	Project 4	2	6	3	5		Low	Low	D
6	Project 5	3	5	8	0		Low	High	C
7	Project 6	3	5	4	4		Low
8	Project 7	4	4	3	5			Low
9	Project 8	4	4	4	4
10	Project 9	5	3	5	3		High	High	A
11	Project 10	4	4	4	4
12
13
14		High effort	Low Effort			High Effort	Low Effort
15	High Impact	A	B		High Impact	Project 1 Project 2 Project 9
16	Low Impact	C	D		Low Impact	Project 5	Project 4
Test 2

Cell Formulas
Range	Formula
G2:G11	G2	=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11	H2	=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11	I2	=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16	F15	=LookUpConcat(B15,SearchRange,ReturnRange,"")

Named Ranges
Name	Refers To	Cells
ReturnRange	='Test 2'!$A$2:$A$11	F15:G16
SearchRange	='Test 2'!$I$2:$I$11	F15:G16

 

[mitzihowe]

Hi,
My apologies, I mistakenly thought TEXTJOIN existed in the 2016 version, my bad. I actually did a google search to check and I mis-read it.

As an alternative, there is a UDF (User Defined Function) available. Rick Rothstein has kindly produced one, the details are here Listed in post #2: -
Vlookup multiple Values then Concatenate all values in One cell

Also here at Ricks Rothstein’s Corner
LookUp Value and Concatenate All Found Results

I have used the UDF from his first post but I would suggest you read through the full post to see the discussion and how it has developed.
There are two Named Ranges:- SearchRange and ReturnRange, I have indicated this as a blue header over columns “A” and “I”
The formula in cell F15 is

 =LookUpConcat(B15,SearchRange,ReturnRange)

The search item is the letter “A” in cell B15

If you wish, you can hard code the Quadrant letter “A” in the formulas like this

 =LookUpConcat(“A”,SearchRange,ReturnRange)

I have ignored the optional parameters but you can include them if you wish.
The VBA code needs to go into a standard module.
The VBA code is:-

I have added & Chr(10) to Rick's code (in red text below) this is to wrap the test.
ReturnVal = ReturnRange(X).Value & Chr(10)

Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                  
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
 
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value & Chr(10)
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
   
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
 
End Function

Forum - Graph projects.xlsx
	A	B	C	D	E	F	G	H	I
1	ReturnRange	High Impact	Low Impact	High Effort	Low Effort		Impact	Effort	SearchRange
2	Project 1	5	3	8	0		High	High	A
3	Project 2	5	3	6	2		High	High	A
4	Project 3	4	4	0	8			Low
5	Project 4	2	6	3	5		Low	Low	D
6	Project 5	3	5	8	0		Low	High	C
7	Project 6	3	5	4	4		Low
8	Project 7	4	4	3	5			Low
9	Project 8	4	4	4	4
10	Project 9	5	3	5	3		High	High	A
11	Project 10	4	4	4	4
12
13
14		High effort	Low Effort			High Effort	Low Effort
15	High Impact	A	B		High Impact	Project 1 Project 2 Project 9
16	Low Impact	C	D		Low Impact	Project 5	Project 4
Test 2

Cell Formulas
Range	Formula
G2:G11	G2	=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11	H2	=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11	I2	=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16	F15	=LookUpConcat(B15,SearchRange,ReturnRange,"")

Named Ranges
Name	Refers To	Cells
ReturnRange	='Test 2'!$A$2:$A$11	F15:G16
SearchRange	='Test 2'!$I$2:$I$11	F15:G16

Hi Taul,

No worries. I actually found a workaround on simulating TextJoin in Excel 2016 and it works a treat.

But most of all, THANK YOU SO MUCH for your help. It was the exact solution I was looking for and you really helped a ton. I am so grateful.