# Basic Impact-Effort Matrix

#### mitzihowe

##### New Member
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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Taul

##### Well-known Member
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
ABCDEFGHI
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low
8Project 74435 Low
9Project 84444
10Project 95353HighHighA
11Project 104444
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=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:G16F15=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

##### New Member
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
ABCDEFGHI
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low
8Project 74435 Low
9Project 84444
10Project 95353HighHighA
11Project 104444
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=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:G16F15=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

##### New Member
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
ABCDEFGHI
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low
8Project 74435 Low
9Project 84444
10Project 95353HighHighA
11Project 104444
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=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:G16F15=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

##### Well-known Member
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
Code:
`` =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
Code:
`` =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)

Code:
``````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
ABCDEFGHI
1ReturnRangeHigh ImpactLow ImpactHigh EffortLow EffortImpactEffortSearchRange
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low
8Project 74435 Low
9Project 84444
10Project 95353HighHighA
11Project 104444
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9
16Low ImpactCDLow ImpactProject 5 Project 4
Test 2
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=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:G16F15=LookUpConcat(B15,SearchRange,ReturnRange,"")
Named Ranges
NameRefers ToCells
ReturnRange='Test 2'!\$A\$2:\$A\$11F15:G16
SearchRange='Test 2'!\$I\$2:\$I\$11F15:G16

#### mitzihowe

##### New Member
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
Code:
`` =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
Code:
`` =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)

Code:
``````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
ABCDEFGHI
1ReturnRangeHigh ImpactLow ImpactHigh EffortLow EffortImpactEffortSearchRange
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low
8Project 74435 Low
9Project 84444
10Project 95353HighHighA
11Project 104444
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9
16Low ImpactCDLow ImpactProject 5 Project 4
Test 2
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=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:G16F15=LookUpConcat(B15,SearchRange,ReturnRange,"")
Named Ranges
NameRefers ToCells
ReturnRange='Test 2'!\$A\$2:\$A\$11F15:G16
SearchRange='Test 2'!\$I\$2:\$I\$11F15: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.

Replies
1
Views
272
Replies
1
Views
75
Replies
6
Views
112
Replies
10
Views
177
Replies
2
Views
101

1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

### 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.

### Which adblocker are you using?

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

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