Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a data dump in Excel that consists of customer_id data with multiple equipment listed against the same customer.

Data looks like this;

customer_id    equipment 
1               xx
1               yy
1               zz
2               xx
2               yy
2               zz
2               xx

I want to data into a worksheet where there's one row per customer_id customer_id equipment_1 #_equipment_1 equipment_2 #_equipment_2

share|improve this question
I tried to format your question so it's easier to understand, but it seems the last paragraph is incomplete. Can you edit it so it makes more sense? Please also include what version of Excel you are using and what you've already tried. – CharlieRB Apr 5 '13 at 16:46
Please provide a same of what the output would be. – Michaelkay Apr 5 '13 at 16:51
See if this helps: – Excellll Apr 6 '13 at 5:09

What you are trying to do is called a Cross-tab in database lingo or a Pivot Table in spreadsheets. Use the PivotTable in Excel. You will need to refer to Excel's help to create it, the instructions are beyond the scope of this post.

share|improve this answer

I'd suggest creating a PivotTable with RecordMacro - results for me as below:

Sub Macro1()
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet1!R1C1:R1048576C2", Version:=xlPivotTableVersion12).CreatePivotTable _
            TableDestination:="Sheet1!R1C4", TableName:="PivotTable2", DefaultVersion _
        Cells(1, 4).Select
        ActiveWorkbook.ShowPivotTableFieldList = True
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("equipment")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("customer_id")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("equipment"), "Count of equipment", xlCount
    End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.