# Consolidate list of KPI Failures or by conditions

#### longhornfan

##### New Member
All,

I have a fairly large list of metrics and I have built in where a metric does not meet the threshold. What I need is to be able to list out all of those metrics by the week and metric name (and include the value). For example:

 Metric 1 Metric 2 Metric 3 Metric 4 Metric 1 Flag Metric 2 Flag Metric 3 Flag Metric 4 Flag Week 1 91 32 91 32 1 Week 2 90 35 90 35 1 Week 3 89 37 89 37 1 Week 4 85 39 85 39 1 Week 5 83 39 83 39 1 Week 6 81 40 81 40 Week 7 89 41 89 41 Week 8 85 42 85 42 1 Week 9 83 43 83 43 Week 10 89 44 89 44 1 Week 11 92 45 92 45 1 Week 12 83 46 83 46 Week 13 81 47 81 47

<TBODY>
</TBODY>

Desired Output

 Week Value Metric Week 1 91 Metric 1 Week 11 92 Metric 1 Week 4 39 Metric 2 Week 5 39 Metric 2 Week 2 90 Metric 3 Week 8 85 Metric 3 Week 3 37 Metric 4 Week 10 44 Metric 4

<TBODY>
</TBODY>

I am not sure the best way to do so. I am doing it manually right now and I need an automated solution either with VBA or a better process.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
longhornfan,

Sample raw data in worksheet Sheet1:

Excel 2007
ABCDEFGHI
1Metric 1Metric 2Metric 3Metric 4Metric 1 FlagMetric 2 FlagMetric 3 FlagMetric 4 Flag
2Week 1913291321
3Week 2903590351
4Week 3893789371
5Week 4853985391
6Week 5833983391
7Week 681408140
8Week 789418941
9Week 8854285421
10Week 983438343
11Week 10894489441
12Week 11924592451
13Week 1283468346
14Week 13
15
Sheet1

After the macro in a new worksheet Results:

Excel 2007
ABC
1WeekValueMetric
2Week 191Metric 1
3Week 290Metric 3
4Week 337Metric 4
5Week 439Metric 2
6Week 539Metric 2
7Week 885Metric 3
8Week 1044Metric 4
9Week 1192Metric 1
10
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
``````Option Explicit
Sub ReorgData()
' hiker95, 04/02/2013
' ReorgData arrays w1 aarray wR barray - longhornfan - ME694695 - SDG15.xlsm
Dim a As Variant, b As Variant, s
Dim i As Long, ii As Long, iii As Long
Dim sf As Long, ef As Long, n As Long
With Sheets("Sheet1")
a = .Cells(1).CurrentRegion
sf = Application.Match("*Flag*", .Rows(1), 0)
ef = .Cells(1, Columns.Count).End(xlToLeft).Column
n = Application.Count(.Range(.Cells(2, sf), .Cells(UBound(a, 1), ef)))
ReDim b(1 To n + 1, 1 To 3)
iii = iii + 1
b(iii, 1) = "Week": b(iii, 2) = "Value": b(iii, 3) = "Metric"
For i = 2 To UBound(a, 1)
For ii = sf To ef
If a(i, ii) <> "" Then
iii = iii + 1
b(iii, 1) = a(i, 1)
s = Split(a(1, ii), " ")
n = Application.Match("Metric " & s(1), .Rows(1), 0)
b(iii, 2) = a(i, n)
b(iii, 3) = s(0) & " " & s(1)
End If
Next ii
Next i
End With
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Results"
With Sheets("Results")
.UsedRange.Clear
.Cells(1).Resize(UBound(b, 1), UBound(b, 2)) = b
.Columns.AutoFit
.Activate
End With
End Sub``````

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Ten run the ReorgData macro.

It ran on the sample data correctly. When attempting to adapt to the real values, it errors on this line with 'Type mismatch'

n = Application.Match("Metric " & s(1), .Rows(1), 0)

The metrics each have their own unique name so the word metric does not actually exist in the real data. For example, AHT. There is a flag called AHT Flag though.

longhornfan,

The metrics each have their own unique name so the word metric does not actually exist in the real data

In order to continue I will have to have a workbook/worksheet with the live data.

sensitive data scrubbed/removed/changed
mark the workbook for sharing

Replies
2
Views
201
Replies
4
Views
263
Replies
5
Views
574
Replies
1
Views
324
Replies
0
Views
195

1,207,392
Messages
6,078,221
Members
446,323
Latest member
fishezuk

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