Consolidate list of KPI Failures or by conditions

[longhornfan]

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.

 

[hiker95]

longhornfan,

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H	I
1		Metric 1	Metric 2	Metric 3	Metric 4	Metric 1 Flag	Metric 2 Flag	Metric 3 Flag	Metric 4 Flag
2	Week 1	91	32	91	32	1
3	Week 2	90	35	90	35			1
4	Week 3	89	37	89	37				1
5	Week 4	85	39	85	39		1
6	Week 5	83	39	83	39		1
7	Week 6	81	40	81	40
8	Week 7	89	41	89	41
9	Week 8	85	42	85	42			1
10	Week 9	83	43	83	43
11	Week 10	89	44	89	44				1
12	Week 11	92	45	92	45	1
13	Week 12	83	46	83	46
14	Week 13
15
Sheet1

After the macro in a new worksheet Results:

Excel 2007
	A	B	C
1	Week	Value	Metric
2	Week 1	91	Metric 1
3	Week 2	90	Metric 3
4	Week 3	37	Metric 4
5	Week 4	39	Metric 2
6	Week 5	39	Metric 2
7	Week 8	85	Metric 3
8	Week 10	44	Metric 4
9	Week 11	92	Metric 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
2. Open your workbook
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.

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.

 

[longhornfan]

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.

 

[hiker95]

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.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.