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

 

[Andrzejek]

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:[tt]
GBS_UK_HHFG
GBS_UK_HHFK[/tt]
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 [tt]GBS_UK_HHFY[/tt] 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]

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]

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:

[pre]
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
[/pre]

---- Andy

There is a great need for a sarcasm font.

 

[remeng]

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

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]

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:

[pre]
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
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Rossco82]

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]

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]

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]

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.
[pre]
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:
[/pre]

Then I added count columns to count occurrences of Role and Title
[pre]
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
[/pre]

If I did this ananysis BEFORE I removed duplicates...
[pre]
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
[/pre]

So what does that tell you?

Unremarkable!

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

 

[Rossco82]

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]

“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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

[pre]
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
[/pre]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Mebe it’s supposed to be...
[pre]
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
[/pre]
Sorry about the PERIODS in the data. I struggled on my iPad to format this for 10 minutes!

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

 

[strongm]

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

 

[Rossco82]

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???.

[pre]
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 [/pre]

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