Dear All,
Firstly, I must thank Simarui for resolving the original question. http://www.excelforum.com/excel-prog...on-matrix.html
Unfortunately, the criteria has changed, (doesn't it always?) and I need some more help.
Attached is a file with 5 tabs
Tab marked "User to System Matrix" is the end result I want to achieve.
I need to to identify in the matrix what systems a user has access to (left to right), or what users are listed in a system (top to bottom) I no longer need to concatenate all the systems to the bottom. However, I need to pull in the User names or descriptions from column E in each of the sheets (which can be added to) into Column B. If the 'User name' has already appeared (e.g. User 1) then it only need be entered once. However, on some systems the User name may be in as "User One" as opposed to "User 1" therefore I would like both to appear.
Finally, I need to pull the entry from Column H in each column into Column C. This should be the same throughout (or blank), however, it would be useful if all entries were also concatenated into this column (so if a user has their parent company entered in one system as Company A and another System as Company B, this would be identified.
I'm not worried about additional control sheets or columns which may or may not be hidden as long as I can come up with something resembling the "User the System Matrix" Tab at the end.
I'm fairly comfortable with Macros, formula etc. However, and I appreciate this is a big ask, if you could comment in the macro what bit each does I would be very grateful as it would help me learn.
My last extract had c 450 unique users from 25 systems, however, this will grow and shrink as the infrastructure develops.
I've left Simarui's original macro in as it might help to be a jump off point.
Kindest regards
Terry
Bookmarks