Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converting grid data to side-by-side lists
Hello everyone, this has been driving me crazy so want to see if any of you
have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! |
#2
|
|||
|
|||
To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Then, sort by the Value column, and delete rows that don't contain an X. Delete the Value column, and add your headings to the other columns. The Chad wrote: Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Thanks Debra!! This works great!
I see the link to John Walkenbach's site includes the code for a VBA macro that automates the process. I tried to create the module using the code but got a "Run-time error '438' Object doesn't support the property or method" error message. Do you (or anyone else) have an idea about how I should fix that? I would love to use the VBA macro but cant seem to get the macro to run without that error. "Debra Dalgleish" wrote: To reorganize the data, you can use the "unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Then, sort by the Value column, and delete rows that don't contain an X. Delete the Value column, and add your headings to the other columns. The Chad wrote: Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
You're welcome. If you're using Excel 2000, you can change the macro
slightly. Instead of: .PivotTables("PivotTable1").DataPivotField _ .PivotItems("Sum of Value").Position = 1 use: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 Because you're using text, instead of numbers, in the data field, it will default to "Count of Value", instead of "Sum of Value". To work around this problem, add the following line at the top of the code: On Error Resume Next Then, after the line: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 add: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 The revised procedure is: '=========================== Sub MakeDataBaseTable() On Error Resume Next Dim SummaryTableRange As Range Dim PivotTableSheet As Worksheet Set SummaryTableRange = ActiveCell.CurrentRegion If SummaryTableRange.Count = 1 Or _ SummaryTableRange.Rows.Count < 3 Then MsgBox "Select a cell in the summary table.", vbCritical Exit Sub End If ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlConsolidation, _ SourceData:=Array(SummaryTableRange _ .Address(True, True, xlR1C1, True))) _ .CreatePivotTable TableDestination:="", _ TableName:="PivotTable1" Set PivotTableSheet = ActiveSheet With PivotTableSheet .PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Row") _ .Orientation = xlHidden .PivotTables("PivotTable1").PivotFields("Column") _ .Orientation = xlHidden End With Range("B4").ShowDetail = True Application.DisplayAlerts = False PivotTableSheet.Delete Application.DisplayAlerts = True End Sub '===================================== The Chad wrote: Thanks Debra!! This works great! I see the link to John Walkenbach's site includes the code for a VBA macro that automates the process. I tried to create the module using the code but got a "Run-time error '438' Object doesn't support the property or method" error message. Do you (or anyone else) have an idea about how I should fix that? I would love to use the VBA macro but cant seem to get the macro to run without that error. "Debra Dalgleish" wrote: To reorganize the data, you can use the "unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Then, sort by the Value column, and delete rows that don't contain an X. Delete the Value column, and add your headings to the other columns. The Chad wrote: Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
It works perfectly! Thank you so much!!
"Debra Dalgleish" wrote: You're welcome. If you're using Excel 2000, you can change the macro slightly. Instead of: .PivotTables("PivotTable1").DataPivotField _ .PivotItems("Sum of Value").Position = 1 use: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 Because you're using text, instead of numbers, in the data field, it will default to "Count of Value", instead of "Sum of Value". To work around this problem, add the following line at the top of the code: On Error Resume Next Then, after the line: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 add: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 The revised procedure is: '=========================== Sub MakeDataBaseTable() On Error Resume Next Dim SummaryTableRange As Range Dim PivotTableSheet As Worksheet Set SummaryTableRange = ActiveCell.CurrentRegion If SummaryTableRange.Count = 1 Or _ SummaryTableRange.Rows.Count < 3 Then MsgBox "Select a cell in the summary table.", vbCritical Exit Sub End If ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlConsolidation, _ SourceData:=Array(SummaryTableRange _ .Address(True, True, xlR1C1, True))) _ .CreatePivotTable TableDestination:="", _ TableName:="PivotTable1" Set PivotTableSheet = ActiveSheet With PivotTableSheet .PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Row") _ .Orientation = xlHidden .PivotTables("PivotTable1").PivotFields("Column") _ .Orientation = xlHidden End With Range("B4").ShowDetail = True Application.DisplayAlerts = False PivotTableSheet.Delete Application.DisplayAlerts = True End Sub '===================================== The Chad wrote: Thanks Debra!! This works great! I see the link to John Walkenbach's site includes the code for a VBA macro that automates the process. I tried to create the module using the code but got a "Run-time error '438' Object doesn't support the property or method" error message. Do you (or anyone else) have an idea about how I should fix that? I would love to use the VBA macro but cant seem to get the macro to run without that error. "Debra Dalgleish" wrote: To reorganize the data, you can use the "unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Then, sort by the Value column, and delete rows that don't contain an X. Delete the Value column, and add your headings to the other columns. The Chad wrote: Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
You're welcome. Thanks for letting me know that it worked.
The Chad wrote: It works perfectly! Thank you so much!! "Debra Dalgleish" wrote: You're welcome. If you're using Excel 2000, you can change the macro slightly. Instead of: .PivotTables("PivotTable1").DataPivotField _ .PivotItems("Sum of Value").Position = 1 use: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 Because you're using text, instead of numbers, in the data field, it will default to "Count of Value", instead of "Sum of Value". To work around this problem, add the following line at the top of the code: On Error Resume Next Then, after the line: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 add: .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 The revised procedure is: '=========================== Sub MakeDataBaseTable() On Error Resume Next Dim SummaryTableRange As Range Dim PivotTableSheet As Worksheet Set SummaryTableRange = ActiveCell.CurrentRegion If SummaryTableRange.Count = 1 Or _ SummaryTableRange.Rows.Count < 3 Then MsgBox "Select a cell in the summary table.", vbCritical Exit Sub End If ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlConsolidation, _ SourceData:=Array(SummaryTableRange _ .Address(True, True, xlR1C1, True))) _ .CreatePivotTable TableDestination:="", _ TableName:="PivotTable1" Set PivotTableSheet = ActiveSheet With PivotTableSheet .PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Sum of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Data") _ .PivotItems("Count of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Row") _ .Orientation = xlHidden .PivotTables("PivotTable1").PivotFields("Column") _ .Orientation = xlHidden End With Range("B4").ShowDetail = True Application.DisplayAlerts = False PivotTableSheet.Delete Application.DisplayAlerts = True End Sub '===================================== The Chad wrote: Thanks Debra!! This works great! I see the link to John Walkenbach's site includes the code for a VBA macro that automates the process. I tried to create the module using the code but got a "Run-time error '438' Object doesn't support the property or method" error message. Do you (or anyone else) have an idea about how I should fix that? I would love to use the VBA macro but cant seem to get the macro to run without that error. "Debra Dalgleish" wrote: To reorganize the data, you can use the "unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Then, sort by the Value column, and delete rows that don't contain an X. Delete the Value column, and add your headings to the other columns. The Chad wrote: Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 Company B Service 3 Company B Service 3 Company C I cant seem to find a good way to do this. Any ideas??? Thanks all! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
Hi This thread was v useful as I was trying to do exactly the same. However, I have a problem. The table I am using is very large so when I run this process I require more rows than are available therfore I have to take chunks of the data and copy and paste as I go to get it to fit in a worksheet. However, I think this can be resolved as the table contains a large number of blanks which I am not interested in and so can ignore when creating the list. Is there a way that when I create the pivot table / list so that all the blanks are ignored? Thanks, Paul -- frankybenali ------------------------------------------------------------------------ frankybenali's Profile: http://www.excelforum.com/member.php...o&userid=18707 View this thread: http://www.excelforum.com/showthread...hreadid=342410 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting data from a pasted document | Excel Worksheet Functions | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Lists | Excel Worksheet Functions | |||
Data Validation Lists | Links and Linking in Excel |