General thoughts/advice

[Rossco82]

So I have been given a task at work. Seemingly straight forward, I just can't think of the best approach.

We have a spreadsheet of users, job titles and roles assigned to them. We are trying to identify people with assigned roles that differ from the norm, but we have nothing set in stone as to what the norm is so are just identifying anything that differs from the rest of the users with the same job title.

Example-FYI Image for reference only and not from actual spreadsheet

In this instance John Davis has GBS_UK_HHFY that he maybe shouldn't have. So it would need to be highlighted against him for that role somehow.

Question is how do I translate that into a spreadsheet with 14000+ lines for hundreds of users and roles? I can manually sit and go through it, but just getting the job titles and assigned roles togtehr has taken two days. So if I can automate it that would be the preference.

The over all aim-

Filter by Job Title and identify differing assigned roles. The roles that differ should be easy to distinguish.

Any thoughts would be appreciated.



A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[Rossco82]

You had replied by the time I did my post. Yup, thats it skip.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[Andrzejek]

That's where properly formatted data representing the issue - or attached Excel file - would be a great help.

And I would not spend $5.75 on a cup of coffee...


---- Andy

There is a great need for a sarcasm font.

 

[Rossco82]

Must be a Costa man lol.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

So what does this tell you?

Do we have enough data to determine what you thisk needs to be determined?
[pre]
System User Role Title cnt role
R0P SMITH-B MCUS:BRUK:CU-FD33-03F Sales Ledger Controller 2
R0P SMITH-B MCUS:BRUK:ZO-03SA-GB-IE-ZO20 Sales Ledger Controller 2
R0P SMITH-B SDMS:CYGB:ALL:CLAIM_HANDLING Sales Ledger Controller 2
R0P SMITH-B SDMS:CYGB:ALL:REPORTING Sales Ledger Controller 2
R0P SMITH-B SDMS:CYGB:APPS:INFOUSER Sales Ledger Controller 2
R0P SMITH-B SSOS:SRGBXX:CREDIT Sales Ledger Controller 2
R0P SMITH-B SSOS:SRGBXX:SALES_ALL Sales Ledger Controller 2
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK Sales Ledger Controller 2
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK_KD Sales Ledger Controller 2
R0P SMITH-B SSOS:SRGBXX:SALES_INFO Sales Ledger Controller 2
R0P SMITH-B BCW:ENDUSER Sales Ledger Controller 4
R0P SMITH-B RACW:GBGBXX:ACC-RECEIVABLE Sales Ledger Controller 2
R0P SMITH-B SSDW:GBGBXX:CICERO-CRE Sales Ledger Controller 2
R0P SMITH-B SSDW:GBGBXX:CICERO-SAC Sales Ledger Controller 2
F0P SMITH-B BCW:ENDUSER Sales Ledger Controller 4
R0P JONES-A MCUS:BRUK:CU-FD33-03F Sales Ledger Controller 2
R0P JONES-A MCUS:BRUK:ZO-03SA-GB-IE-ZO20 Sales Ledger Controller 2
R0P JONES-A SDMS:CYGB:ALL:CLAIM_HANDLING Sales Ledger Controller 2
R0P JONES-A SDMS:CYGB:ALL:REPORTING Sales Ledger Controller 2
R0P JONES-A SDMS:CYGB:APPS:INFOUSER Sales Ledger Controller 2
R0P JONES-A SSOS:SRGBXX:CREDIT Sales Ledger Controller 2
R0P JONES-A SSOS:SRGBXX:SALES_ALL Sales Ledger Controller 2
R0P JONES-A SSOS:SRGBXX:SALES_CLERK Sales Ledger Controller 2
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_KD Sales Ledger Controller 2
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_XX Sales Ledger Controller 1
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_YY Sales Ledger Controller 1
R0P JONES-A SSOS:SRGBXX:SALES_INFO Sales Ledger Controller 2
R0P JONES-A BCW:ENDUSER Sales Ledger Controller 4
R0P JONES-A RACW:GBGBXX:ACC-RECEIVABLE Sales Ledger Controller 2
R0P JONES-A SSDW:GBGBXX:CICERO-CRE Sales Ledger Controller 2
R0P JONES-A SSDW:GBGBXX:CICERO-SAC Sales Ledger Controller 2
F0P JONES-A BCW:ENDUSER Sales Ledger Controller 4

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

This tells me I now need to look at the below roles :

SSOS:SRGBXX:SALES_CLERK_XX
SSOS:SRGBXX:SALES_CLERK_YY

I can then determine if these roles are needed for a sales ledger controller. Or if someone has more/less access than they need.

Once I have done that we can restrict/expand access, create a solid set of roles per job title going forward.

This is then written into procedure, and we avoid all of this again in future.

I assume this is just a countof in an additional column? Then if the count is 1 the line is made to be bold???

The reason I've said about side by side comparison previously is because you might have 25 people doing one role, and want to compare them one by one (i.e confirm what the roles should be for user one then compare the rest)

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

There are several approches. Might be to do a COUNTIF(). Then take the low counts and do something else to them.

But using a very small data sample and then expecting to get ONE approch is ludicrous!

Analysis of this type is labor intensive. You look for patterns. You analyze different patterns in different ways, perhaps. Depends on the circumstances.

This will take many itterations, IMNSHO.

Side by side with gobs of data? Don’t think so, although in certain circumstances it might be helpful. Depends on the circumstances.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

Skip, not sure how giving you another several hundred names and roles would help?. The data sample given gives all fields and an example of the difference. What I'm asking is simply for a way to identify the differences quickly. So if it is a countif and use the low values to highlight, then that works.

The whole point of my question is so there is no labour intensive analysis. If someone came up with something for the sample I gave, could that not be expanded to match the whole range?. There is also no patterns, that is why I need to do a comparison. If there was a pattern this would all be vastly easier as I could probably guess the oddities.

edit: Read that back and it sounded quite argumentative, absolutely not the intention. More curiosity than anything.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Well with the current data sample, what conclusions can you make, when comparisons yielded the following conclusions?

The outliers are...
[pre]
BCW:ENDUSER 4
SSOS:SRGBXX:SALES_CLERK_XX 1
SSOS:SRGBXX:SALES_CLERK_YY 1
[/pre]
Frequency seems to the key parameter.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

I got exactly the conclusion I needed as it identified the "odd" assigned roles??? This would spur a whole raft of other work in the back ground. So Still don't get why that couldn't be scaled up and why more data would be needed?. In effect you have given the solution then said you can't give a solution without more data???. Very likely due to how I have explained it all as usual.



A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

“This would spur a whole raft of other work in the back ground.“

That is exactly what I would expect! This process is a series of steps like this, winnowing the data and then making separate decisions regarding the resultant groupings of data.

This could also be done with a PivotTable using CountOf. Got to play with the various tools at hand to get through the winnowing process.

If counting is gonna do it for you, then that’s the tool for THIS STEP in the pricess. Any following step may need some other tool, but who knows at this point.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

Seems to me that if we know how many distinct users hold a particular job title, we can compare that value against the number of people with that job title with a particular role. And where those numbers match then we are good, otherwise bad. A pivot table like this, for example:

which would let you drill down like:

 

[Rossco82]

String/Skip, sorry for the large delay, but I wasn't going to let this beat me. I now have a solution, all be it basic, it does exactly what I need. I'm sure you gents can make it more elegant, but I'm no where near as advanced.

C2 and N2 are used for data validation pulling the headers (users names) from the table tab. (I have set this as a named range for ease of listing in data validation)

Each data validation has a "Get Details" button underneath with the below code for their respective search criteria -

Sub FindSecond()
Dim FindString As String
Dim Rng As Range
FindString = Range("N2")
If Trim(FindString) <> "" Then
    With Sheets("Table").Range("1:1") 'searches all of Row 1
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True 'value found
            Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Comparison").Select
    Range("N5").Select
    ActiveSheet.Paste
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
End Sub

I have then added two columns to the middle with simple countif formula to return any values that don't match for each column, so now we have -

Users just select a user, press the button to load/copy their details and boom, they have the differences they were after.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Great!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]