Combining grouped data

[PeteBogg]

Please forgive me if this is a bit simple for you guys, but I am new here and despite looking through lots of threads I can't find the answer to my problem.

I have been presented with a spreadsheet of ticket purchases in which all the quantities are 1. If more than 1 ticket was purchased by a buyer (identified by an ID) another line of 1 ticket is recorded.... such as this example:

<table width="335" border="0" cellpadding="0" cellspacing="0"><col style="width: 71pt;" width="95"> <col style="width: 95pt;" width="126"> <col style="width: 86pt;" width="114"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 71pt;" width="95" height="20">IDNumber</td> <td class="xl66" style="width: 95pt;" width="126">Type of Ticket</td> <td class="xl66" style="width: 86pt;" width="114">Quantity</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">111</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">111</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">111</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">111</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">B</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">B</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">C</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">333</td> <td class="xl65">C</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">333</td> <td class="xl65">C</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">333</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> </tbody></table>
I need to change it so that the multiple purchases are grouped... like this:

<table width="335" border="0" cellpadding="0" cellspacing="0"><col style="width: 71pt;" width="95"> <col style="width: 95pt;" width="126"> <col style="width: 86pt;" width="114"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 71pt;" width="95" height="20">IDNumber</td> <td class="xl66" style="width: 95pt;" width="126">Type of Ticket</td> <td class="xl66" style="width: 86pt;" width="114">Quantity</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">111</td> <td class="xl65">A</td> <td class="xl65">4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">A</td> <td class="xl65">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">B</td> <td class="xl65">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">222</td> <td class="xl65">C</td> <td class="xl65">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">333</td> <td class="xl65">C</td> <td class="xl65">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">333</td> <td class="xl65">A</td> <td class="xl65">1</td> </tr> </tbody></table>
How do I do that ? I can get a result by using a pivot table, but that doesn't give me exactly this format, which I need for importing into another application.

Thanks in advance for all and any suggestions.

Pete

 

[S M C]

WOuld this pivot format work?

Edit: Seems I can't attach a file

 

[PeteBogg]

WOuld this pivot format work?

Edit: Seems I can't attach a file

Gosh... the suspense is killing... are we allowed to contact by email in this forum?
Cheers,
Pete

 

[VoG]

Seems I can't attach a file

Try Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[S M C]

Thanks VoG.

Anyway, Pete, my pivot is made like this

IDNumber,Type of Ticket >> Row Label

Quantity >> Column Label

Quantity (Sum) >> Value/Data

 

[PeteBogg]

Thanks VoG.

Anyway, Pete, my pivot is made like this

IDNumber,Type of Ticket >> Row Label

Quantity >> Column Label

Quantity (Sum) >> Value/Data

Thanks S M C.... I haven't got it yet, but you have led me to explore the layout possibilities of the pivot table... haven't got time tonight but will do some more tomorrow.
Cheers,
Pete

 

[PeteBogg]

Thanks S M C.... I haven't got it yet, but you have led me to explore the layout possibilities of the pivot table... haven't got time tonight but will do some more tomorrow.
Cheers,
Pete

Right, S M C... I've got a pivot table to do this job fine, now, but with one little problem remaining:

Row Labels are ID, Type of Ticket
Value is Quantity
Report Filter and Column Label is empty.

But... Where there is more than one Type of Ticket per ID, the second and subsequent lines leave the ID field blank. I need that ID to be filled in.... what's the best way to do that?
Cheers,
Pete