Converting grid data to side-by-side lists

[The Chad]

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]

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

[The Chad]

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]

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

[The Chad]

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]

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

[frankybenali]

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