Data Customization for Pareto

[Chefsohail]

Hi Team,

I am working on a data set and need help in arranging the data set for a pareto chart. I have a dynamic data and if i arrange it every time, it will be a manual task. I need assistance where i can eliminate the manual work.

Below is the situation -

I have my data arranged in following pattern. I perform audits and mark errors on Transactions. Lets say Number column is the transaction number. And one transaction can have multiple errors. I have given an example below of 4 errors types.

Table 1

Number	Error1	Error2	Error3	Error4	Error_Count	CTQ_Count	Score (number format %)
1					=COUNTIF(B2:E2,0)	=COUNT(B2:E2)	=SUM(B2:E2)/COUNT(B2:E2)
2					=COUNTIF(B3:E3,0)	=COUNT(B3:E3)	=SUM(B3:E3)/COUNT(B3:E3)
3					=COUNTIF(B4:E4,0)	=COUNT(B4:E4)	=SUM(B4:E4)/COUNT(B4:E4)
4					=COUNTIF(B5:E5,0)	=COUNT(B5:E5)	=SUM(B5:E5)/COUNT(B5:E5)
5					=COUNTIF(B6:E6,0)	=COUNT(B6:E6)	=SUM(B6:E6)/COUNT(B6:E6)

Eg with data: Here score of 1 means No Error. If transaction has errors, the score given to error type (Error1, Error2, Error3, Error4) is 0.

Table 2

Number	Error1	Error2	Error3	Error4	Error_Count	CTQ_Count	Score
1​	1​	1​	1​	1​	0​	4​	100%​
2​	0​	0​	0​	0​	4​	4​	0%​
3​	1​	0​	0​	0​	3​	4​	25%​
4​	1​	1​	0​	0​	2​	4​	50%​
5​	1​	1​	1​	0​	1​	4​	75%​

I need to perform an error analysis now to understand which error is repeated the most. And I need this to be dynamic. If someone can share a solution that'll be great. If nothing rings a bell, and if you can help me convert this to below, that'll be awesome.

Table 3

Number	Error Type
2​	Error1
2​	Error2
2​	Error3
2​	Error4
3​	Error2
3​	Error3
3​	Error4
4​	Error3
4​	Error4
5​	Error4

So basically -
1. Transaction #1 did not have any errors. Hence data is not included in Table 3.
2. Transaction #2 had 4 errors. The column wise data is now split to rows with multiple entries of the same transaction with different error type.
3. Transaction #3 has 3 errors, Transaction #4 has 2 errors and Transaction #5 has 1 error.

I have tried to simplify the query here. However in actual, my data set contains multiple error types and many other columns needed for the project.
Please do let me know if we have any questions

 

[Chefsohail]

May be a possible solution that can work.

Can we create a vba code for this?

I need to paste my data in Sheet1.. and once the code is executed , it creates the data as I want in another sheet.. If someone can help with it, I'll be glad.

Please find below the headers. For data privacy purposes I cant add alot of information, hence I am giving common header names. However I will update the corresponding headers in the VBA code.

Column 1

Column 2

Column 3

Column 4

Column 5

Column 6

Error 1

Error 2

Error 3

Error 4

Error 5

Error 6

Error 7

Error 8

Error 9

Error 10

Error 11

Error 12

Error 13

Error 14

Quality Met %

Remarks

Error_Count

CTQ_Count

AQS Target

Cluster Lead

WK#

 

[StephenCrump]

For a Pareto chart, don't you want to summarise your errors in order of frequency, like this?

	A	B	C	D	E
1	No	Error1	Error2	Error3	Error4
2	1	1	1	1	1
3	2	0	0	0	0
4	3	1	0	0	0
5	4	1	1	0	0
6	5	1	0	1	0
7
8			Chart:
9			Error4	4
10			Error2	3
11			Error3	3
12			Error1	1
Sheet1

Cell Formulas
Range	Formula
C9:D12	C9	=SORT(TRANSPOSE(VSTACK(B1:E1,BYCOL(B2:E6,LAMBDA(c,COUNTIF(c,0))))),2,-1)
Dynamic array formulas.

 

[Chefsohail]

Thanx @StephenCrump for writing.

I have used a similar solution for the time being. However it doesnt make my Pareto Chart Dynamic. Eg: If i need pareto based on period or for a smaller team.
I'll be adding a slicer and timeline to make it dynamic.

 

[Chefsohail]

Any help with the macro? Anyone?

 

[StephenCrump]

I need to paste my data in Sheet1.. and once the code is executed , it creates the data as I want in another sheet.. If someone can help with it, I'll be glad.

I have used a similar solution for the time being. However it doesnt make my Pareto Chart Dynamic. Eg: If i need pareto based on period or for a smaller team.
I'll be adding a slicer and timeline to make it dynamic.

You'll need to be a lot more specific about what you want your macro to do.

- What does a realistic data set look like?
- What do you want the macro to do with it, and what does the required output look like for the data provided?
- What do you mean by making the Pareto chart dynamic "based on period or for a smaller team"?

 

[Chefsohail]

You'll need to be a lot more specific about what you want your macro to do.

- What does a realistic data set look like?
- What do you want the macro to do with it, and what does the required output look like for the data provided?
- What do you mean by making the Pareto chart dynamic "based on period or for a smaller team"?

Thanx for writing @StephenCrump, below are the details.

- What does a realistic data set look like?
The realistic data set is as below.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

Number

Resolved By

Resolved Date

Reopen Count

Breach Status

PRB

First Update in Worklog

Detailed Analysis (Technical & Business Language)

Closure Notes

On Hold State

Other Team Involvement

Re-Prioritize

Delayed/ Normal Assignment

No Action by L2

Store Call

3 Strikes (Store Call)

PRB/KBA Linking

KBA Updation

Store Personal full name

Valid Reopened - Reasoning

Quality Met %

Remarks

Error_Count

CTQ_Count

AQS Target

Cluster Lead

WK#

INC12345671

SpiderMan

10/27/2022​

0​

Breached

PRB1234561

1​

1​

1​

Na

Na

Na

Na

Na

1​

Na

1​

Na

1​

Na

100%​

Excellent

0

6

Black Panther

41

INC12345672

SuperMan

10/27/2022​

1​

Not Breached

PRB1234562

1​

1​

1​

Na

Na

Na

Na

Na

1​

Na

1​

Na

1​

Na

100%​

Excellent

0

7

Black Panther

41

INC12345673

BatMan

10/27/2022​

2​

Not Breached

PRB1234563

1​

1​

1​

Na

Na

Na

Na

Na

1​

Na

1​

Na

1​

Na

100%​

Excellent

0

7

Black Panther

41

INC12345674

Captain America

10/27/2022​

3​

Not Breached

0​

0​

1​

1​

Na

Na

Na

Na

Na

0​

Na

1​

Na

1​

Na

67%​

Feedback shared

2

6

Doctor Strange

41

INC12345675

Thor

10/27/2022​

4​

Breached

1​

1​

1​

0​

1​

Na

Na

Na

0​

Na

0​

Na

1​

Na

63%​

Feedback shared

3

6

Doctor Strange

41

INC12345676

Wonder Woman

10/27/2022​

5​

Not Breached

PRB1234566

0​

1​

1​

Na

Na

Na

Na

Na

0​

Na

1​

Na

1​

Na

67%​

Feedback shared

2

7

Doctor Strange

41

INC12345677

Black Widow

10/27/2022​

6​

Breached

1​

1​

1​

Na

Na

Na

Na

Na

1​

Na

1​

Na

0​

Na

83%​

Feedback shared

1

7

Doctor Strange

41

INC12345678

Iron Man

10/27/2022​

7​

Not Breached

0​

0​

1​

1​

Na

Na

Na

Na

Na

1​

Na

0​

Na

1​

Na

67%​

Feedback shared

2

7

Doctor Strange

41

INC12345679

Hulk

10/27/2022​

8​

Not Breached

PRB1234569

1​

1​

1​

Na

Na

Na

Na

Na

1​

Na

1​

Na

1​

Na

100%​

Excellent

0

7

Doctor Strange

41

What do you want the macro to do with it?
The workbook needs to have 2 Sheets. Input and Output
I will copy and paste the above data in the input sheet.
In the input sheet -
The macro needs to look for '0' (zeros starting Column G through Column T
Lets say for first entry INC12345671.
The Quality Met % (Column U) for this is 100%, so the macro doesnt need to do anything and can move to the next one...
INC12345672 is also 100%
INC12345673 is also 100%
However the 4th one is INC12345674 = 67% as it has errors.
So the macro should check column G for it. There will be three possibilities - either the entry will be 1 or 0 or Na
if it is 1/Na, the macro can move to the next column. However if it is '0' then the macro needs to copy data from Column A through F and column Z of the same row and paste it in an output sheet.

Number	Resolved By	Resolved Date	Reopen Count	Breach Status	PRB
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​

The macro should create additional column in it named 'Error Type'

Number	Resolved By	Resolved Date	Reopen Count	Breach Status	PRB	Error Type
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​

and the corresponding error (header) needs to be mentioned in the error type column and data from corresponding Column Z to be pasted next to it.

Number	Resolved By	Resolved Date	Reopen Count	Breach Status	PRB	Error Type	Cluster Lead
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​	First Update in Worklog	Doctor Strange

Now the macro needs to move to the next column H.
Now since column H, I, J, K, L, M and N either have 1 or Na... it can skip and directly move to column O which has '0'

Now the macro will again copy Column A through F of the same row and paste it in an output sheet and the corresponding error (header) needs to be mentioned in the error type column.

Number	Resolved By	Resolved Date	Reopen Count	Breach Status	PRB	Error Type	Cluster Lead
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​	First Update in Worklog	Doctor Strange
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​	Store Call	Doctor Strange

Then until Column T, the rest of the data has either Na or 1. So can skip and move to the next row.

what does the required output look like for the data provided?
The required data should look like below.

Number	Resolved By	Resolved Date	Reopen Count	Breach Status	PRB	Error Type	Cluster Lead
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​	First Update in Worklog	Doctor Strange
INC12345674	Captain America	10/27/2022​	3​	Not Breached	0​	Store Call	Doctor Strange
INC12345675	Thor	10/27/2022​	4​	Breached		On Hold State	Doctor Strange
INC12345675	Thor	10/27/2022​	4​	Breached		Store Call	Doctor Strange
INC12345675	Thor	10/27/2022​	4​	Breached		PRB/KBA Linking	Doctor Strange
INC12345676	Wonder Woman	10/27/2022​	5​	Not Breached	PRB1234566	First Update in Worklog	Doctor Strange
INC12345676	Wonder Woman	10/27/2022​	5​	Not Breached	PRB1234566	Store Call	Doctor Strange
INC12345677	Black Widow	10/27/2022​	6​	Breached		Store Personal full name	Doctor Strange
INC12345678	Iron Man	10/27/2022​	7​	Not Breached	0​	First Update in Worklog	Doctor Strange
INC12345678	Iron Man	10/27/2022​	7​	Not Breached	0​	PRB/KBA Linking	Doctor Strange

What do you mean by making the Pareto chart dynamic "based on period or for a smaller team"?
I only need the macro to prepare the data as above.
The Pareto chart that I have prepared has timeline and slicers connected to it. So when I need Pareto analysis for a Team. The chart will present data accordingly. Likewise since we have resolved date in the data. I can select the period that I need the pareto for and for a specific team. The system will display the information accordingly.
Hope that clarifies.

Please do let me know if we have any other questions.

 

[StephenCrump]

Try:

Sub Test()

    Dim vIn As Variant, vOut As Variant, Header As Variant
    Dim NoCols As Long, Col_1 As Long, Col_2 As Long, i As Long, j As Long, k As Long, count As Long

    NoCols = 26
    Col_1 = 7
    Col_2 = 20
    
    With Worksheets("Input")
        Header = Range(.Cells(1, Col_1), .Cells(1, Col_2)).Value
        vIn = .Range("A2").Resize(.Range("A" & Rows.count).End(xlUp).Row - 1, NoCols).Value2
    End With
    ReDim vOut(1 To UBound(vIn) * (Col_2 - Col_1 + 1), 1 To Col_1 + 1)
        
    For i = 1 To UBound(vIn)
        If vIn(i, Col_2 + 1) < 1 Then
            For j = Col_1 To Col_2
                If vIn(i, j) = 0 Then
                    count = count + 1
                    For k = 1 To Col_1 - 1
                        vOut(count, k) = vIn(i, k)
                    Next k
                    vOut(count, Col_1) = Header(1, j - Col_1 + 1)
                    vOut(count, Col_1 + 1) = vIn(i, NoCols)
                End If
            Next j
        End If
    Next i

    If count > 0 Then
        Worksheets("Output").Range("A2").Resize(count, UBound(vOut, 2)).Value = vOut
        MsgBox "Finished"
    Else
        MsgBox "No errors"
    End If
    
End Sub

 

[Chefsohail]

Awesome. Thanx for the great help @StephenCrump . You've saved a lot of my manual effort.
I would like to learn this skill.

Will you please advice how do i start?

 

[StephenCrump]

The best way to learn VBA is to start coding. Start simple, and build up as your knowledge and experience grows.

There are many VBA resources that may be helpful listed on this thread: Big Shoutout!