transform table into list / how to "unpivot" a table

[fab54]

Hi All,
I have a bit of a challenge for you...at least it is for me

I have a table of quantity of cars sold by brand (more than two dimensions) that I would like to tranform into a list (5 columns).

Please have a look at the data below

Your help is HIGHLY appreciated...as always

PS:I get the xls files in the input format and I would like a list in order to use a pivot tables


INPUT
<table x:str="" style="border-collapse: collapse; width: 288pt;" width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="6" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Show</td> <td class="xl25" style="width: 48pt;" width="64">Date</td> <td class="xl25" style="width: 48pt;" width="64">Seller</td> <td class="xl24" style="width: 48pt;" width="64">BMW</td> <td class="xl24" style="width: 48pt;" width="64">Audi</td> <td class="xl24" style="width: 48pt;" width="64">Mercedes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">A</td> <td class="xl26" x:num="">5</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26" x:num="">5</td> <td class="xl26" x:num="">10</td> <td class="xl26" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">1</td> <td class="xl26" x:num="">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">4</td> <td class="xl26" x:num="">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr><td valign="top">Desired Output:
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Show</td> <td class="xl25">Date</td> <td class="xl25">Seller</td> <td class="xl25">Brand</td> <td class="xl25">Sold</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26"> A</td> <td class="xl26">BMW</td> <td class="xl26" x:num="">5</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26">BMW</td> <td class="xl26" x:num="">5</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">10</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">1</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26">Mercedes</td> <td class="xl26" x:num="">3</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">4</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26">Mercedes</td> <td class="xl26" x:num="">4</td> <td class="xl26">
</td> </tr> </tbody></table>

 

[hiker95]

gusio,

Thanks for the confidence.

This is a very old thread, and, your text display does not show what worksheet, cells, rows, and, columns, the raw data is in, and, what worksheet, cells, rows, and, columns, the results are in.

Please start your own NEW thread, and, in your own NEW thread include:

What version of Excel and Windows are you using?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message, and, I will have a look.