Return value that most often occurs adjacent to another value

[sanchovonbadgerson]

I need a little help please,

I have a large list of employees, each month they make mistakes in processing I have a report generated monthly which contains columns relating to their names and the types of error they have made - where each person may have made multiple errors of varying types

ideally I want to have a second tab which wil automatically pull through the names in ranking order of errors made in the dataset, they return the total number of errors, the type of most common error and the count of that type.
I appreciate that a pivot table could resolve most of this, but due to the volume of data each month and the eventual person who will have to compile the data in to a report this would be overly time consuming.


all help is greatly appreciated. Due to the system used at work - VBA is out, I need a formula based solution

thus far I can return a list of names in order of occurences by adding a check column to the source data with this:

=IF(F2="","",IF(COUNTIF(F$2:F2,F2)=COUNTIF($F$2:$F$9999,F2),COUNTIF($F$2:$F$9999,F2)+(ROW()/1000),""))

then on a results page this:

=IF(ROWS($1:1)>COUNT(Worksheet!A:A),"",INDEX(Worksheet!F:F,MATCH(LARGE(Worksheet!A:A,ROWS($1:1)),Worksheet!A:A,0)))

extracting the total number of errors relatively simple then with this

=IF(B3="","",COUNTIFS(Worksheet!F:F,B3,Worksheet!J:J,"Error made"))

but i then require the modal error from the list - and im struggling

a sample of the source data


[TABLE="width: 1755"]
<tbody>[TR]
[TD][/TD]
[TD]Reference[/TD]
[TD]Team[/TD]
[TD]Category[/TD]
[TD]Error By[/TD]
[TD]Error By Name[/TD]
[TD]Logged By[/TD]
[TD]Department[/TD]
[TD]Error[/TD]
[TD]Status[/TD]
[TD]Date Reported[/TD]
[TD]Date error Made[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4169321[/TD]
[TD]CS[/TD]
[TD]Procedure Error[/TD]
[TD]KW5[/TD]
[TD]Kieran Wilkinson[/TD]
[TD]cd7[/TD]
[TD]Auditing[/TD]
[TD]Hold time too long[/TD]
[TD]Error Made[/TD]
[TD]31/10/2017[/TD]
[TD]30/10/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3621797[/TD]
[TD]CS[/TD]
[TD]Procedure Error[/TD]
[TD]AA4[/TD]
[TD]Ashley Allsopp[/TD]
[TD]cd7[/TD]
[TD]Auditing[/TD]
[TD]Card DPA[/TD]
[TD]Error Made[/TD]
[TD]31/10/2017[/TD]
[TD]30/10/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]254413[/TD]
[TD]CS[/TD]
[TD]Procedure Error[/TD]
[TD]AA4[/TD]
[TD]Ashley Allsopp[/TD]
[TD]cd7[/TD]
[TD]Auditing[/TD]
[TD]Card DPA[/TD]
[TD]Error Made[/TD]
[TD]31/10/2017[/TD]
[TD]30/10/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4392925[/TD]
[TD]CS[/TD]
[TD]Diaries[/TD]
[TD]KY2[/TD]
[TD]Keeley Sweeney[/TD]
[TD]cd7[/TD]
[TD]Auditing[/TD]
[TD]Not added diary when should have[/TD]
[TD]Error Made[/TD]
[TD]31/10/2017[/TD]
[TD]30/10/2017[/TD]
[/TR]
</tbody>[/TABLE]



I appreciate this is me basically asking someone to do a job for me, I can usually figure most things out but I am entirely self-taught and this one has me stumped.

Cheers in advance

 

[sanchovonbadgerson]

Re: Return value that most often occurs adjacent to antoher value

slight update - ive been working with this formula, but the result is then simply the bottom value in the adjacent cell as opposed to the most frequently occuring

=IFERROR(LOOKUP(1E+100,SEARCH(B3,Worksheet!F:F),Worksheet!I:I),"")

where B3 is the name produced as a result of this:

=IF(ROWS($1:1)>COUNT(Worksheet!A:A),"",INDEX(Worksheet!F:F,MATCH(LARGE(Worksheet!A:A,ROWS($1:1)),Worksheet!A:A,0)))

 

[sanchovonbadgerson]

Re: Return value that most often occurs adjacent to antoher value

antoher update - i think ive cracked it, based on the below - with varitations depending on desired outcome

=INDEX(Worksheet!I:I,MODE(IF(Worksheet!F:F=Sheet1!B3,IF(Worksheet!I:I<>"",MATCH(Worksheet!I:I,Worksheet!I:I,{0,0})))))

only problem is im running this in four columns will multiple variables over around 200 rows, so the sheet is running painfully slow.
that said the person who asked me of it was rpeviously using pivot tabels and it took about two hours every month to compile the data - this way, copy paste and then wait 10 mins, i'll update if I find a better solution

 

[shg]

Does it need to be that complicated?


[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

M​

[/td][td="bgcolor:#C0C0C0"]

N​

[/td][td="bgcolor:#C0C0C0"]

O​

[/td][td="bgcolor:#C0C0C0"]

P​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Error By​

[/td][td]

Procedure Error​

[/td][td]

Diaries​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]KW5[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td]N2: =COUNTIFS($D$2:$D$5, $M2, $C$2:$C$5, N$1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]AA4[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]KY2[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[/table]

 

[sanchovonbadgerson]

I appreciate what you are saying - but there are just north of 600 staff, with relatively high turnover due to the nature of the business (so i cant fix the columns and do a simple count of users- life would be easier that way), there are also approximately 200 different types of 'error', about 20 catagories with about 10 types in each catagory. The above selection isnt great as it doesnt really represent the data.
As an example, in October, the worst ofending staff member made 71 errors, 30 of which fell into the same type, position 50 made 20 errors - 10 of the same type.
total lines of data to look at for the month are a couple of thousand

we are looking for who they are, where they work, total errors, count of most frequently occuring error, what that error is & what department logged the most frequently occuring error against them

To be fair the above is taking about 3 mins to fully calculate - the lady who usually gets the report together spends about 2 hours each month with pivot tables sorting out - net saving 1hr 57 mins.

I would bet heavily it can be done in a much more elegant way than I have found, but without all of the source data and output information It would be difficult to explain fully - and unfortunately some of the information contained in the source is sensitive.

 

[shg]

It seems to me that the report would be as complicated to absorb as the raw data.

I would think the objective would be to learn what the most prevalent types of errors are (perhaps cost-weighted, with a thought to how they could be reduced over time), and who is the most error-prone (with a thought as to retraining or whacking them).

Changing a sea of data to another sea of data wouldn't seem to me like it would help.

 

[sanchovonbadgerson]

This is one of those "ours is not to reason why" moments, I was asked if I could save a friend (an assistant to a higher up) some time in collating the data. IT department said it couldnt be done, and at first glance so did the internet. Anyway - she's chuffed with what i have done so far. I'll keep an eye out for better solutions. Basically the people who appear in the top 50 on this report are in for some bother.

Cheers for attention though.