ExcelBanter - Converting grid data to side-by-side lists

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Converting grid data to side-by-side lists (https://www.excelbanter.com/excel-discussion-misc-queries/11652-converting-grid-data-side-side-lists.html)

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!

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

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

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

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

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

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