Listing names and id and corresponding software

[Guest]

I have a workbook with several worksheets. Each worksheet is divided by the
software they have access to. There's overlapping names in each worksheet as
some names have access to more than one software. How can I make this into a
report listing the names on the left column, the names of the software listed
across the top row, and a mark below each column if that person has access to
that software.
There's a login ID down Column A, and in some instances there's a secondary
ID down Column B, then the names in Column C. Each sheet represents a
different software as mentioned previously.

Thanks!!!

 

[Guest]

First job is to decide which piece of information on the individual software
sheets it is that you're going to use to identify a person; ID? secondary ID?
Name? or some combination of the three? The preference would be to have
whatever item or combination available on all software sheets in use.

If it is a single item, then it's easy to get the mark in the particular
columns with a MATCH() or even VLOOKUP() formula. If it is a combination of
entries on the software sheets, then a SUMPRODUCT() formula will probably be
the one to use.

A little more information about how you're going to determine who is who
over on the summary sheet will help make a final decision as to what type of
worksheet function will best meet your needs.

 

[Guest]

I would like the output to include the Id and name and then if there's a
secondary ID, to list all 3. Thanks for the help.

 

[Guest]

OK, multiple sheets with individual software package use being tracked. One
sheet to roll it all up into.

In the individual sheets you have an ID in column A. We will presume that
all users have an ID assigned, and that each ID is unique.

On the 'rollup' sheet you want ID, secondary ID if available, and name and
then out across the sheet you want to mark which software each user has
access to.

This would work best if you had another sheet which we will call UserSheet
that listed all users, with ID in column A, secondary ID (where there is one)
in B, and name in C. This sheet provides you with a 'controlled' list that
you should use to get entries from for all other sheets; software and
roll-up. You can also use it to make sure that all user IDs in column A are
unique and that you haven't entered someone onto two different software
sheets with 2 different IDs.

We will presume you've created such a UserSheet and that you have 50 users
and so your list of IDs/2ndary IDs and names goes from A2 over and down to
C51. You may want to look into Named Ranges to set things up on that sheet
to make adding to the list/deleting from it/editing in it easier in the
future - and so that you don't have to do any formula changes on the rollup
sheet when adds/deletes occur. But for now we'll just work assuming no named
ranges.

To get your secondary IDs and names into the rollup sheet you will put a
person's ID number into column A. On that same row in column B put this
formula (adjust for the real 'UserSheet')
=VLOOKUP(A2,'UserSheet'!A$2:C$51,2,0)
That will bring over the secondary ID for that user from column B on the
UserSheet.
in column C of the same row use
=VLOOKUP(A2,'UserSheet'!A$2:C$51,3,0)
which will bring over the name.
You can fill those formulas on down the worksheet as you add user IDs into
column A.

For each column from D on over, one column for each software package/sheet,
you need to use formulas similar to this, but you'll need to change the sheet
name in each column to go along with the sheet related to that software
package:
=IF(ISNA(VLOOKUP($A2,'SWareSheet1'!A$2:A$100,1,0)),"","X")
this says to try to get a match to the UserID in column A of the rollup
sheet in column A (assumes entries from row 2 to 100 on the specific software
sheet - change as needed), if there is no match (an #N/A error happens),
then display an empty cell, but if a match was found, then show an X in the
cell.

You could use the MATCH function instead of the VLOOKUP in that formula like
this (might be a touch faster)
=IF(ISNA(MATCH($A2,'SWareSheet1'!A$2:A$100,0)),"","X")