Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Require Excel Best-fit calculator

Status
Not open for further replies.
MCubitt

MCubitt

Programmer
Mar 14, 2002
1,081
0
0
GB
I have a spreadsheet similar to this: (may have to copy into fixed font text)


Role1 Role2 Role3 Role4 Role5 Role6
Obj1 X
Obj2 X X X X
Obj3 X X
Obj4 X X X
Obj5 X X X
Obj6 X X X
Obj7 X X
Obj8 X X

Each role (1-6) can have upto 8 objects selected.

My request is to add a new row which indicates which role is most similar to each.

Clearly, role1 and role4 are very similar so the next role for Role1 would be role4 and vice versa.

I presume a point system of matches would be needed, so for Role1 each other role and each object is compared, where equal add 1 point. Which ever role had the highest number of points is the closest match (if not a draw). If 0 then there is no match at all.

Does this make sense? The actual spreadsheet has 60 roles and 9,000 objects making it a nightmare task manually!

Any help much appreciated.



 
Sort by date Sort by votes
Hi,

LOTS of questions.

You stated "Clearly, role1 and role4 are very similar...". It's not clear to me. How is it clear to you?

Specifically, how would you use the above matrix? What information comes from it?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Okay, Role1 used Obj1,2,5 and 6 while Role4 uses Obj2, 5 and 6. So there are 3 points scored by Role4 when matching with Role1.

The other roles (matching to role1) have the following scores:
Role2 2 (Obj5,6)
Role3 1 (Obj2)
Role5 1 (Obj2)
Role6 0

The background is, users have completed a large matrix defining which objects particular roles may have access to (actually, rather than X or space, it is R(ead), F(ull) or Space).

Rather than set up 60 roles individually, I want to set up a few and then copy these to the closest matches and tweak them, if that makes sense?
 
Upvote 0 Downvote
Skip, that's not the problem (copy and paste in to notepad: three objects match exactly, only one object in role1 has no match, no objects in role4 are unmatched.) the real question is what the heck does this mean:

...add a new row which indicates which role is most similar to each...

Add a new row where?
Does that become a new object?
How to indicate?

something like
Code: 
      Role1  Role2  Role3  Role4  Role5  Role6
Obj1    +      -      -      +      -      -
 
Upvote 0 Downvote
I think that we have a misunderstanding of the data.

I copied your data to Notepad and back
Code: 
      Role1  Role2  Role3  Role4  Role5  Role6
Obj1    X
Obj2    X             X       X     X
Obj3           X      X
Obj4                  X             X      X
Obj5    X      X              X
Obj6    X      X              X
Obj7           X                           X
Obj8                  X             X
So I observe that
role1 4
role2 4
role3 4
role4 3
role5 3
role6 2

What do we do with this?


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Zath,

A row anywhere, above the headings, after the last object, I really do not mind, just somewhere so I can see it.

Eg
Role1 Role2 Role3 Role4 Role5 Role6
Obj1 X
Obj2 X X X X
Obj3 X X
Obj4 X X X
Obj5 X X X
Obj6 X X X
Obj7 X X
Obj8 X X
Match Role4 Role1 Role5 Role1 Role3 Role2

Of course it might even have a % of how good the match was. 100% means perfect match and have to ask the Question, why do we have a duplicate!
 
Upvote 0 Downvote
Is your goal here to get a formula to put in a cell that would compare a column of data against many columns of data and give you the closest match to that column?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Skip,

It is not the number of Xs each role has, but compared to the other roles how many match. Is my example clear enough, with
Role1 4 (Obj2,5,6)
Role2 2 (Obj5,6)
Role3 1 (Obj2)
Role4 1 (Obj2,5,6)
Role5 1 (Obj2)
Role6 0

 
Upvote 0 Downvote
Even I (who loves writing formulas) don't think I will tackle that one. I think a VBA (hush Skip) solution is your best bet.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Blue,

Thanks. I'd rather know it's not possible than to spend my life trying!

 
Upvote 0 Downvote
Hmmm. Do you really have 60 roles in the sense of having 60 distinct job descriptions, or do you merely have 60 distinct combinations of object authorizations that have been granted over time?

It would seem to me, based on the limited information you have provided so far, that you ought to be able to start over with job definitions and see what job actually needs access to what objects.

For example, if you have 5 departments and each department has a manager and a clerical role, that would reduce to 10 groups for security purposes.

Another way to approach it is to see that in order to reduce the number of roles involved, you will have to grant additional access to some roles in order to make them look like some others. Maybe you could add a column to contain the count of X's (or two columns to contain the counts of R's and F's) and then sort on that column. Then "look around" to see where it makes sense to grant additional access. Surely you can group the 9000 objects into clusters with similar requirements to reduce the number of rows (I would consider doing that first.)
 
Upvote 0 Downvote
Zath,

Unfortunately in a company of my size and with a very lareg ERP system, we have already consolidated the roles and objects as much as we can.

We have nearer 20 departments and each has approx Manager, Supervisor and Clerk. This is of course at a basic level.

I think I will return to my original plan which was auto-filtering and selecting non-blanks in role 1, then gradually omitting other roles.
 
Upvote 0 Downvote
OK, I lied, it intrigued me enough to try something...

Here is what I did:

I took your example data and plugged it in. Role headers in B1 through B7 and Obj headers starting in A2 through A9

I created another table that is like a map milage table. I have role 1-8 as column headers and role 1-8 as row headers.

now in column role 1, row role 2, I put:

SUMPRODUCT(($B$2:$B$9="x")*(C$2:C$9="x"))/COUNTIF($B$2:$B$9,"x")

This tells you that for role 1, role 2 has a 50% match, or 2 out of the four possible.

For column header role 1, row header role 3, I put:

=SUMPRODUCT(($B$2:$B$9="x")*(D$2:D$9="x"))/COUNTIF($B$2:$B$9,"x")

This shows a 25% match of Role 3 to Role 1, or 1 out of 4.

What you end up with is an array that shows what percentage each role has with all other roles.

If you would like an example sheet, let me know.





[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Thanks Blue,

I think you just blew my mind. Will look tomorrow if I can.

Thanks again.
 
Upvote 0 Downvote
I also combined and averaged the two percentages in the new role array which gave one percentage for each role. I then identified the best match for each role and its compatibility.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bijugate
  • Question
Excel update question
Replies
0
Views
236
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
239
combo
combo
shytott
  • Question
Excel - Toggle Button Code 1
Replies
10
Views
739
shytott
shytott
renigar
  • Question
Excel Power Query refresh error
Replies
3
Views
360
SoftwareRT
SoftwareRT
arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
272
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top