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!

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

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
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!

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

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
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!

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 Befo=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



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

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
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!

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.
"Bernie Deitrick" wrote:

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 Befo=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



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

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
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!

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

"NeedExcelHelp07" wrote in
message ...
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.
"Bernie Deitrick" wrote:

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 Befo=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



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

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in
message
...
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!