This is my first question, so sorry if I have a "classic" problem. I'm an applications security guy, so I'm not usually in Excel.
My question is mainly one of format and sorting. It's what I call, the 3rd dimension problem.
Currently, I have three different excel workbooks (examples attached).
Workbook 1: I have a list of users as row headers and a list of computer systems as column headers. This way I know which users are in which systems (indicated by an x).
Workbook 2: I have a list of roles listed as row headers, and again, the same list of computer systems as column headers. This way I know which roles are in which system.
Workbook 3: I have a list of users as row headers and a list of roles as column headers. This way I can see which users are assigned to which roles (see tabs in attachment).
The relationship between roles, users, systems.
One role can be assigned to many users and/or one user can have many roles.
Many users can be on many systems.
Many roles can be on many systems.
Problem: I need to figure out how to consolidate the three spreadsheets into one. Preferably, what I would like is a quick way to find which users are on which systems, and then which roles are assigned to those users. The solutions needs to work for spreadsheets with a few thousand records. Preferably no scripts if possible.
With my current setup, I have to keep a User-to-Role (Workbook 3) sheet for EACH and EVERY different system, because users can be assigned different roles depending on which system they are on.
If anyone is aware of how to fix this I would appreciate it. Also, how to appropriately arrange the data within the workbook would be nice.
Let me know if you need anymore detail.
Thank you very much.
Bookmarks