General thoughts/advice
General thoughts/advice
(OP)
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.
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
RE: General thoughts/advice
So my first point would be to set such norm. And then modify it if needed.
If Account manager should have roles:
GBS_UK_HHFG
GBS_UK_HHFK
then that's the rule.
Go down the rows and identify Account manages with any other role(s).
If you (or somebody) decides that the GBS_UK_HHFY is also OK for Account manager to have, add it to the 'norm' and check again.
---- Andy
There is a great need for a sarcasm font.
RE: General thoughts/advice
This is exactly the problem. To do that is hours and hours of work. We don't want to set the standard here, this isn't our remit. We just need to identify who has excessive roles assigned to them to start with (i.e me and you do the same job, I have 6 roles you have 4, what extra two roles do I have). At some point the people in the right departments will decide what the norm is based on our findings.
And going through the rows will also take excessively long, as I say it's 14000+ rows. So Ideally I want to automate, but I just need someone to point me in the right direction. I can't think how to do it. I can go through as you said, this was my initial thought, but it will just take way too long.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Who cares if you have 100 rows or 14 000 rows of data in Excel? With a little of VBA code it will take either 2 seconds or 20 seconds to process. But you will need to establish some logic / requirements.
Let's say you get the distinct list of positions, one of which is Account manager.
Then you can get the distinct list of people who are Account managers.
Then you can go down the records and see / count / detect the roles each of that person has.
And if you sort your rows before you run your code thru it, it may be even easier.
That all depends on what you want to get.
With your example you can easily get the number of roles per person:
---- Andy
There is a great need for a sarcasm font.
RE: General thoughts/advice
Could a pivot table help?
Setting the name as your row, you could then see what job titles are assigned to them by adding the title as a sub row.
You could also set your column to a sum value to see what might have multiple assignments.
Would that work?
Mike
RE: General thoughts/advice
First off, we have a image, totally (well mostly) worthless to a person wishing to COPY ‘n’ PASTE to set up a test sheet in order to help you.
Secondly, the three columns of data, albeit small in number, are nameless, although reading the post yields possible names. Yet having the names included in the example would have helped to clarify the task at hand.
Thirdly, having supplied a small example and a description of your problem and intent, ALSO supplying an example of a solution to the set supplied would help direct us toward some helpful alternatives for your analysis.
For instance you stated, “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” but you never explain the rational or logic you used to arrive at that conclusion.
Given what exists, I’d suggest a PivotTable using CountOf and putting the column 2 data in the Filter Area. Then analyse results for each column 2 data value by selecting such in the filter. The rest rests on your subjective reasoning.
Once you have arrived at a logic that seems to work on a representative sub set, then you’re in a position to perhaps code a solution of some sort.
Skip,
for a NUance!
RE: General thoughts/advice
This has changed massively now anyway. I thought with the length of time it would take it was just easier to manually go and do some of the stuff required.
Skip.
1. Noted. I didn't put text as the formatting went off every time I previewed it, so I used an image instead. Didn't see the point in putting it in if it couldn't be used. Now don't get me wrong, it will ENTIRELY be something I am doing wrong, but it was as worthless as an image. As an image showed it correctly, so that's what you got.
2. You can name them whatever you like, it will make no difference to the task. Name, Title, Role is what the three displayed will be. I will be clearer on this in future.
3. I don't have one hence asking for general advice on an approach.
So let me clear all of this up................
Text example including header names (if formatting is out educate me as to how I get it to display correctly on here)
System User Role Title Key filter
R0P SMITH-B MCUS:BRUK:CU-FD33-03F Sales Ledger Controller
R0P SMITH-B MCUS:BRUK:ZO-03SA-GB-IE-ZO20 Sales Ledger Controller
R0P SMITH-B SDMS:CYGB:ALL:CLAIM_HANDLING Sales Ledger Controller
R0P SMITH-B SDMS:CYGB:ALL:REPORTING Sales Ledger Controller
R0P SMITH-B SDMS:CYGB:APPS:INFOUSER Sales Ledger Controller
R0P SMITH-B SSOS:SRGBXX:CREDIT Sales Ledger Controller
R0P SMITH-B SSOS:SRGBXX:SALES_ALL Sales Ledger Controller
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK Sales Ledger Controller
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK_KD Sales Ledger Controller
R0P SMITH-B SSOS:SRGBXX:SALES_INFO Sales Ledger Controller
R0P SMITH-B BCW:ENDUSER Sales Ledger Controller
R0P SMITH-B RACW:GBGBXX:ACC-RECEIVABLE Sales Ledger Controller
R0P SMITH-B SSDW:GBGBXX:CICERO-CRE Sales Ledger Controller
R0P SMITH-B SSDW:GBGBXX:CICERO-SAC Sales Ledger Controller
F0P SMITH-B BCW:ENDUSER Sales Ledger Controller
R0P JONES-A MCUS:BRUK:CU-FD33-03F Sales Ledger Controller
R0P JONES-A MCUS:BRUK:ZO-03SA-GB-IE-ZO20 Sales Ledger Controller
R0P JONES-A SDMS:CYGB:ALL:CLAIM_HANDLING Sales Ledger Controller
R0P JONES-A SDMS:CYGB:ALL:REPORTING Sales Ledger Controller
R0P JONES-A SDMS:CYGB:APPS:INFOUSER Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:CREDIT Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_ALL Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_CLERK Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_KD Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_XX Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_YY Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_INFO Sales Ledger Controller
R0P JONES-A BCW:ENDUSER Sales Ledger Controller
R0P JONES-A RACW:GBGBXX:ACC-RECEIVABLE Sales Ledger Controller
R0P JONES-A SSDW:GBGBXX:CICERO-CRE Sales Ledger Controller
R0P JONES-A SSDW:GBGBXX:CICERO-SAC Sales Ledger Controller
F0P JONES-A BCW:ENDUSER Sales Ledger Controller
(NB some users have 2 roles, some might have 50)
System = system the role is assigned on (not really relevant as we look at all of it, but it son the sheet so I have added it)
User = User who has the job
Role = Assigned role/user funtion
Title = Job Title
Key Filter = Some roles only have one user so I have added a flag to those roles so they can be filtered out to save looking for duplicates that don't exist (1 = single user for title)
I would like to be able to select a Title, this will then display the Users for that Title, but also extract/highlight/list any differences between assigned roles. The Logic being used is very simple. If someone is in the same role and they have something someone else doesn't, it isn't the norm. So for the example above the below two lines would be highlighted/listed/exported/made known somehow (as they are the two that differ from SMITH-B so would be deemed as not normal for the role) -
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_XX Sales Ledger Controller
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_YY Sales Ledger Controller
I can filter out of course, but this places potentially loads of users on top of each other (imagine the above sample with 50 users and 500 roles between them) with no quick and easy way to determine which roles are unique. I did try conditional formatting which for some reason doesn't work. If I pull a list of values and do them side by side its fine, but it won't work in a single list. Plus with the roles named as they are its not as easy as just looking through. It would take forever. Management may wish to compare a "user" on the sheet with one person, then a different one, then change roles etc. So the solution needs to be quite fluid.
My initial thought was two drop down boxes, you select a user and this list out the roles assigned to them. This leaves space for error though as they could select two users with different titles. Ideally though this would be the way to do it as it would list side by side a users roles for their job and make it easy to identify what doesn't match. That being said if it was one user with 100 roles again it becomes messy. So being able to identify the unique roles without human input is key.
I hope I have covered everything?.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Please use Pre tag to format your text like this:
---- Andy
There is a great need for a sarcasm font.
RE: General thoughts/advice
Thanks! Didn't know this bit.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_XX Sales Ledger Controller (extra role assigned)
R0P JONES-A SSOS:SRGBXX:SALES_CLERK_YY Sales Ledger Controller (extra role assigned)
is the problem, rather than
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK_XX Sales Ledger Controller (missing this role)
R0P SMITH-B SSOS:SRGBXX:SALES_CLERK_YY Sales Ledger Controller (missing this role)
RE: General thoughts/advice
The issue all stems from a very large business with extensive roles which overtime have been added to/deducted from etc. People now just apply things because that is what they usually do. My remit is to identify the oddities (the little nugget you kind people are looking at). Then evaluate which roles are the correct set per job title. The first step in that is identifying who does what job (complete), what roles are assigned to each user (complete) and what roles differ across users per job (not complete).
I'm not fussed at present if user A is missing something or if user B has it as an additional role. I just need to identify the differences.
What I had envisaged is two data validation boxes that simply list the roles per user and highlights those that are unique (something like the below) -
I can add the data validation boxes, but that is about the extent of my knowledge. I don't know how to make the list of assigned roles populate, nor highlight unique values from that list. I assume the data needs to be linked to the user in some way other than just being on the same row?.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Then I use Data > Remove Duplicates to find unique occurrences of all three values.
Then I added count columns to count occurrences of Role and Title
If I did this ananysis BEFORE I removed duplicates...
So what does that tell you?
Unremarkable!
Skip,
for a NUance!
RE: General thoughts/advice
I can remove duplicates using the button, but the problem is that in doing so I will be editing the data. It will be filter by title/user, then remove duplicates. If I forgot to undo the removal then they are gone from the sheet. All entries need to stay where they are hence giving the above as an idea.
So very literally all columns as above, then to the side two more columns with drop down boxes to select a user. Their roles then appear underneath (as above).
Not sure I can explain this any better. I am a complete novice, so apologies if I am making this much harder than it needs to be.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Maybe your table needs fixin’
I’m using User, Role, Title. Isn’t that the data you’ve been talking about?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
R0P SMITH-B MCUS:BRUK:CU-FD33-03F Sales Ledger Controller
R0P SMITH-B MCUS:BRUK:ZO-03SA-GB-IE-ZO20 Sales Ledger Controller
R0P SMITH-B SDMS:CYGB:ALL:CLAIM_HANDLING Sales Ledger Controller
R0P SMITH-B SDMS:CYGB:ALL:REPORTING Sales Ledger Controller
then when Andy use PRE AT 13:20 it became -
System User Role Title Key filter
R0P SMITH-B MCUS: BRUK: CU-FD33-03F Sales Ledger Controller
R0P SMITH-B MCUS: BRUK: ZO-03SA-GB-IE-ZO20 Sales Ledger Controller
R0P SMITH-B SDMS: CYGB: ALL:CLAIM_HANDLING Sales Ledger Controller
R0P SMITH-B SDMS: CYGB: ALL:REPORTING Sales Ledger Controller
R0P SMITH-B SDMS: CYGB: APPS:INFOUSER Sales Ledger Controller
You have then used the first 3 delimted columns, which aren't correct.
The first three from the correct text would be-
1. R0P
2. SMITH-B
3. MCUS:BRUK:CU-FD33-03F
From the version using pre
1. R0P
2. SMITH-B
3. MCUS:
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Yes, I used Andy’s table, for want of a better example.
Use PRE tags... and then PREVIEW before Submitting!
And YES, you’ll probably need to FIX the data so that data APPEARS in the proper column in PREVIEW!
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
This is my GUESS. But my GUESS and $5.75 gets me a cup of coffee in a big city.
Can we see the entire table like this, or however it should appear, please?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
Sorry about the PERIODS in the data. I struggled on my iPad to format this for 10 minutes!
Skip,
for a NUance!
RE: General thoughts/advice
RE: General thoughts/advice
The data after is just more names with more job titles and more assigned roles. I can add more text as a sample if needed?
In all fairness I should have checked Andy's entry once he had "pre'd" it earlier, and not assumed it was right.
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
And I would not spend $5.75 on a cup of coffee...
---- Andy
There is a great need for a sarcasm font.
RE: General thoughts/advice
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
Do we have enough data to determine what you thisk needs to be determined?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
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
RE: General thoughts/advice
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,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
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
RE: General thoughts/advice
The outliers are...
Frequency seems to the key parameter.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: General thoughts/advice
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
RE: General thoughts/advice
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,
for a NUance!
RE: General thoughts/advice
which would let you drill down like:
RE: General thoughts/advice
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 -
CODE --> VBA
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
RE: General thoughts/advice
Skip,
Just traded in my OLD subtlety...
for a NUance!