General thoughts/advice

Question

General thoughts/advice

Rossco82 (IS/IT--Management)

(OP)

14 Nov 18 14:29

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

Andrzejek (Programmer) · Answer 1 · 2018-11-14 15:30:24

Quote (Rossco82 )
we have nothing set in stone as to what the norm is

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.

Rossco82 (IS/IT--Management) · Answer 2 · 2018-11-14 16:18:40

Hi Andy,

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

Andrzejek (Programmer) · Answer 3 · 2018-11-14 17:04:41

It may not be that bad.
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:

Bob Smith	Account manager	GBS_UK_HHFG	
Bob Smith	Account manager	GBS_UK_HHFK	2
John Davis	Account manager	GBS_UK_HHFG	
John Davis	Account manager	GBS_UK_HHFK	
John Davis	Account manager	GBS_UK_HHFY	3

---- Andy

There is a great need for a sarcasm font.

remeng (TechnicalUser) · Answer 4 · 2018-11-14 18:38:15

Hi,

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

SkipVought (Programmer) · Answer 5 · 2018-11-14 19:25:33

Hi,

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,
_{Just traded in my OLD subtlety...

for a NUance!}

Rossco82 (IS/IT--Management) · Answer 6 · 2018-11-15 12:37:00

Hi, bit delayed, sorry.

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

Andrzejek (Programmer) · Answer 7 · 2018-11-15 13:20:12

Quote (Rossco82 )
I didn't put text as the formatting went off every time I previewed it

because you did not format the text properly.

Please use Pre tag to format your text like this:

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

---- Andy

There is a great need for a sarcasm font.

Rossco82 (IS/IT--Management) · Answer 8 · 2018-11-15 13:29:34

Quote (---- Andy)
because you did not format the text properly.

Please use Pre tag to format your text like this:

Thanks! Didn't know this bit.

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

strongm (MIS) · Answer 9 · 2018-11-15 13:53:59

So we are safe to assume that all users will have been assigned all their proper roles? You are only worried about extra roles that have been assigned? Because if that is not the case how do we know that

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)

Rossco82 (IS/IT--Management) · Answer 10 · 2018-11-15 14:36:53

Either way really, you would still highlight the same two lines, in which case that gives me cause to look at those roles and evaluate who is correct. It might be that the extra roles assigned are for another reason. This is why I've said anything additional is not the norm, not that either user role set is correct. (Semantics really)

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

SkipVought (Programmer) · Answer 11 · 2018-11-15 15:02:14

So I took your three columns of interest and copied them to another sheet.

Then I use Data > Remove Duplicates to find unique occurrences of all three values.

User	Role	Title
SMITH-B	MCUS:	BRUK:
SMITH-B	SDMS:	CYGB:
SMITH-B	SSOS:	SRGBXX:
SMITH-B	BCW:	ENDUSER
SMITH-B	RACW:	GBGBXX:
SMITH-B	SSDW:	GBGBXX:
JONES-A	MCUS:	BRUK:
JONES-A	SDMS:	CYGB:
JONES-A	SSOS:	SRGBXX:
JONES-A	BCW:	ENDUSER
JONES-A	RACW:	GBGBXX:
JONES-A	SSDW:	GBGBXX:

Then I added count columns to count occurrences of Role and Title

User	Role	Title	Role count	Title count
SMITH-B	MCUS:	BRUK:	2       	2
SMITH-B	SDMS:	CYGB:	2       	2
SMITH-B	SSOS:	SRGBXX:	2       	2
SMITH-B	BCW:	ENDUSER	2       	2
SMITH-B	RACW:	GBGBXX:	2       	4
SMITH-B	SSDW:	GBGBXX:	2       	4
JONES-A	MCUS:	BRUK:	2       	2
JONES-A	SDMS:	CYGB:	2       	2
JONES-A	SSOS:	SRGBXX:	2       	2
JONES-A	BCW:	ENDUSER	2       	2
JONES-A	RACW:	GBGBXX:	2       	4
JONES-A	SSDW:	GBGBXX:	2       	4

If I did this ananysis BEFORE I removed duplicates...

User	Role	Title	cnt role	cnt title
SMITH-B	MCUS:	BRUK:	4       	4
SMITH-B	MCUS:	BRUK:	4       	4
SMITH-B	SDMS:	CYGB:	6       	6
SMITH-B	SDMS:	CYGB:	6       	6
SMITH-B	SDMS:	CYGB:	6       	6
SMITH-B	SSOS:	SRGBXX:	12       	12
SMITH-B	SSOS:	SRGBXX:	12      	12
SMITH-B	SSOS:	SRGBXX:	12      	12
SMITH-B	SSOS:	SRGBXX:	12      	12
SMITH-B	SSOS:	SRGBXX:	12      	12
SMITH-B	BCW:	ENDUSER	4       	4
SMITH-B	RACW:	GBGBXX:	2       	6
SMITH-B	SSDW:	GBGBXX:	4       	6
SMITH-B	SSDW:	GBGBXX:	4       	6
SMITH-B	BCW:	ENDUSER	4       	4
JONES-A	MCUS:	BRUK:	4       	4
JONES-A	MCUS:	BRUK:	4       	4
JONES-A	SDMS:	CYGB:	6       	6
JONES-A	SDMS:	CYGB:	6       	6
JONES-A	SDMS:	CYGB:	6       	6
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	SSOS:	SRGBXX:	12      	12
JONES-A	BCW:	ENDUSER	4       	4
JONES-A	RACW:	GBGBXX:	2       	6
JONES-A	SSDW:	GBGBXX:	4       	6
JONES-A	SSDW:	GBGBXX:	4       	6
JONES-A	BCW:	ENDUSER	4       	4

So what does that tell you?

Unremarkable!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Rossco82 (IS/IT--Management) · Answer 12 · 2018-11-15 15:14:16

Not a thing, most of the text has been stripped from the roles (not sure if you did that just to test with?). But it also isn't what I'm trying to do.

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

SkipVought (Programmer) · Answer 13 · 2018-11-15 15:19:16

“most of the text has been stripped from the roles”

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!}

Rossco82 (IS/IT--Management) · Answer 14 · 2018-11-15 15:24:53

Ah I see why. Its when it has been formatted on here. Look at my earlier text at 12:37 -

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

SkipVought (Programmer) · Answer 15 · 2018-11-15 15:38:33

Please post your table properly so that we don’t have to guess what’s in each column!

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!}

SkipVought (Programmer) · Answer 16 · 2018-11-15 15:48:21


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

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!}

SkipVought (Programmer) · Answer 17 · 2018-11-15 15:57:13

Mebe it’s supposed to be...

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

Sorry about the PERIODS in the data. I struggled on my iPad to format this for 10 minutes!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

strongm (MIS) · Answer 18 · 2018-11-15 16:00:13

Yep, that's the one Skip. At least IMHO

Rossco82 (IS/IT--Management) · Answer 19 · 2018-11-15 16:05:32

Unfortunately no I can't post the whole thing as it has names etc. This is why I gave a sample of the text but changed the names. Plus its 14103 rows long, do you really want all that???.

System   User      Role                                Title
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

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

Rossco82 (IS/IT--Management) · Answer 20 · 2018-11-15 16:06:09

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 (Programmer) · Answer 21 · 2018-11-15 16:08:12

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 (IS/IT--Management) · Answer 22 · 2018-11-15 16:10:36

Must be a Costa man lol.

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

SkipVought (Programmer) · Answer 23 · 2018-11-15 16:20:53

So what does this tell you?

Do we have enough data to determine what you thisk needs to be determined?


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

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Rossco82 (IS/IT--Management) · Answer 24 · 2018-11-15 16:32:17

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 (Programmer) · Answer 25 · 2018-11-15 16:49:54

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,
_{Just traded in my OLD subtlety...

for a NUance!}

Rossco82 (IS/IT--Management) · Answer 26 · 2018-11-15 17:01:50

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 (Programmer) · Answer 27 · 2018-11-15 17:12:12

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

The outliers are...

BCW:ENDUSER                 4
SSOS:SRGBXX:SALES_CLERK_XX  1
SSOS:SRGBXX:SALES_CLERK_YY  1

Frequency seems to the key parameter.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Rossco82 (IS/IT--Management) · Answer 28 · 2018-11-15 17:17:01

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 (Programmer) · Answer 29 · 2018-11-15 17:24:26

“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,
_{Just traded in my OLD subtlety...

for a NUance!}

strongm (MIS) · Answer 30 · 2018-11-15 20:29:54

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 (IS/IT--Management) · Answer 31 · 2018-11-21 13:15:18

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 -

CODE --> VBA

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 (Programmer) · Answer 32 · 2018-11-21 15:20:57

Great!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Come Join Us!

Posting Guidelines

General thoughts/advice