How do I make a report listing Names and corresponding programs?

G

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.

Thanks!
 
B

Bernie Deitrick

How are the individual sheets laid out? Just a list of names down column A?

HTH,
Bernie
MS Excel MVP
 
G

Guest

Yes, the names are listed down column A. Each sheet is labeled after the
Software (Software A, Software B, etc.)
 
B

Bernie Deitrick

Run the macro below. Change the "Yes" to the mark that you want: an "X", or perhaps a wingding font
checkmark.... You may need to apply formatting to the cells (either manually or with the macro) if
you use the wingding....

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim mySht As Worksheet
Dim mySumSheet As Worksheet
Dim myCell As Range
Dim myDest As Range
Dim myRow As Long

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True

ActiveSheet.Copy Before:=Sheets(1)
Set mySumSheet = ActiveSheet
mySumSheet.Name = "Summary"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = "Summary" Then GoTo SkipMe:
Set myDest = mySumSheet.Cells(1, 256).End(xlToLeft)(1, 2)
myDest.Value = mySht.Name
Set myDest = myDest.EntireColumn

For Each myCell In mySht.Range("A1").CurrentRegion.Columns(1).Cells
If myCell.Row <> 1 Then
If Not IsError(Application.Match(myCell.Value, mySumSheet.Range("A:A"), False)) Then
myDest.Cells(Application.Match(myCell.Value, mySumSheet.Range("A:A"), False)).Value =
"Yes"
Else
myRow = mySumSheet.Cells(Rows.Count, 1).End(xlUp)(2).Row
mySumSheet.Cells(myRow, 1).Value = myCell.Value
myDest.Cells(myRow).Value = "Yes"

End If
End If
Next myCell
SkipMe:
Next mySht

End Sub
 
G

Guest

Thanks a lot! How can I run this Macro if in each sheet 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. So same macro but there's two more columns of
information.
 
B

Bernie Deitrick

What information do you want to extract from each sheet? Just the name, or
do you need to get the login information as well when the name is new?

Bernie
 
G

Guest

I want to extract the name and the login and then if there's a secondary ID,
that information as well.
 
B

Bernie Deitrick

Try this version, below.

HTH,
Bernie
MS Excel MVP

Sub MakeSummarySheetV2()
Dim mySht As Worksheet
Dim mySumSheet As Worksheet
Dim myCell As Range
Dim myDest As Range
Dim myRow As Long

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True

ActiveSheet.Copy Before:=Sheets(1)
Set mySumSheet = ActiveSheet
mySumSheet.Name = "Summary"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = "Summary" Then GoTo SkipMe:
Set myDest = mySumSheet.Cells(1, 256).End(xlToLeft)(1, 2)
myDest.Value = mySht.Name
Set myDest = myDest.EntireColumn

For Each myCell In mySht.Range("C1").CurrentRegion.Columns(3).Cells
If myCell.Row <> 1 Then
If Not IsError(Application.Match(myCell.Value, _
mySumSheet.Range("C:C"), False)) Then
myDest.Cells(Application.Match(myCell.Value, _
mySumSheet.Range("C:C"), False)).Value = "Yes"
Else
myRow = mySumSheet.Cells(Rows.Count, 1).End(xlUp)(2).Row
mySumSheet.Cells(myRow, 1).Value = myCell.Offset(0, -2).Value
mySumSheet.Cells(myRow, 2).Value = myCell.Offset(0, -1).Value
mySumSheet.Cells(myRow, 3).Value = myCell.Value
myDest.Cells(myRow).Value = "Yes"
End If
End If
Next myCell
SkipMe:
Next mySht

End Sub
 
G

Guest

Ok this macro works. Now the only problem is how do I merge the information
of all the duplicates? There are 30,000 plus users so I can't go one by one.
In coumn A is the Id which is unique to the user. There's an X marked which
indicates they have access to that software. Is there a way to merge the
information from the duplicates?
Thanks!
 
B

Bernie Deitrick

I thought that was what we were doing, based on column C's values (the
names). There should be no duplicated values in column C after the macro is
run.... Do you want to do the table based on column A instead? I'm a bit
confused, so let me know.

It would help if you created two very small text table examples of before,
and then after the merge, to show exactly what you want.

Bernie
 
G

Guest

What I'm getting:

PRIMARY SECONDARY ID NAME Software A Software B Software C
Software d....
BZ0T23 Jeanne Doe X
BZ0T23 Doe JEANNE X
X
BZ0T23 Doe JEANNE
X

Ok the above is what I'm getting after running the Macro.

So I'm asking how to merge them to make it look like below:
PRIMARY SECONDARY ID NAME Software A Software B Software C
Software d....
BZ0T23 Jeanne Doe X X
X X

Other than this, the Macro works well.

Thanks alot for the help.
 
B

Bernie Deitrick

Then try the version below, which acts on column A... As far as choosing
between Jeanne Doe
and
Doe JEANNE
whichever is found first will be used.

HTH,
Bernie
MS Excel MVP


Sub MakeSummarySheetV3()
Dim mySht As Worksheet
Dim mySumSheet As Worksheet
Dim myCell As Range
Dim myDest As Range
Dim myRow As Long

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True

ActiveSheet.Copy Before:=Sheets(1)
Set mySumSheet = ActiveSheet
mySumSheet.Name = "Summary"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = "Summary" Then GoTo SkipMe:
Set myDest = mySumSheet.Cells(1, 256).End(xlToLeft)(1, 2)
myDest.Value = mySht.Name
Set myDest = myDest.EntireColumn

For Each myCell In mySht.Range("A1").CurrentRegion.Columns(1).Cells
If myCell.Row <> 1 Then
If Not IsError(Application.Match(myCell.Value, _
mySumSheet.Range("A:A"), False)) Then
myDest.Cells(Application.Match(myCell.Value, _
mySumSheet.Range("A:A"), False)).Value = "X"
Else
myRow = mySumSheet.Cells(Rows.Count, 1).End(xlUp)(2).Row
mySumSheet.Cells(myRow, 1).Value = myCell.Value
mySumSheet.Cells(myRow, 2).Value = myCell.Offset(0, 1).Value
mySumSheet.Cells(myRow, 3).Value = myCell.Offset(0, 2).Value
myDest.Cells(myRow).Value = "X"
End If
End If
Next myCell
SkipMe:
Next mySht

End Sub
 
G

Guest

Awhile ago I had help with the below macro. I'm running it again, but I have
9 columns now. How can I modify this macro so that the data in column c that
is in Sheet B but not in sheet A is displayed? I still need the rest of the
functions of the macro to work.

Example of what is happening:
In Sheet A:
ID NAME Location

ABCDK2 Hank Doe

In Sheet B
ID NAME Location
ABCDK2 Hank Doe California

When I run the macro, California is not being displayed in the Summary,that
field is blank because in Sheet A, it is blank and that is what is read first.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Listing names and id and corresponding software 3
VBA - retrieve data on last create named range from previous inserted named range 0
Worksheet Names using Macros 5
Excel Is this possible?? 3
Reference the same cell based on tab name 1
Auto worksheet name? 1
LIST OF PERSON'S NAMES 1
worksheet name variable external data cell 2

Top