| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a report listing Names and corresponding programs?
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding programs?
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
I want to extract the name and the login and then if there's a secondary ID,
that information as well. "Bernie Deitrick" wrote: 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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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 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("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 "NeedExcelHelp07" wrote in message ... I want to extract the name and the login and then if there's a secondary ID, that information as well. "Bernie Deitrick" wrote: 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! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! "Bernie Deitrick" wrote: 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 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("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 "NeedExcelHelp07" wrote in message ... I want to extract the name and the login and then if there's a secondary ID, that information as well. "Bernie Deitrick" wrote: 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! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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 "NeedExcelHelp07" wrote in message ... 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! "Bernie Deitrick" wrote: 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 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("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 "NeedExcelHelp07" wrote in message ... I want to extract the name and the login and then if there's a secondary ID, that information as well. "Bernie Deitrick" wrote: 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! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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 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 = "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 "NeedExcelHelp07" wrote in message ... 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a report listing Names and corresponding program
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! "Bernie Deitrick" wrote: 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 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 = "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 "NeedExcelHelp07" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I'd like to make a report card . | New Users to Excel | |||
| Listing variable names for 3 greatest values in a column? | Excel Worksheet Functions | |||
| Listing the names of your worksheets | Excel Discussion (Misc queries) | |||
| listing all names in a worksheet | Excel Discussion (Misc queries) | |||
| Listing of Sheet names | Excel Worksheet Functions | |||
Linear Mode
