Convert Excel Tables to Pivot Table Lists

Excel Tables to Pivot Lists

Hello,

I'm trying to convert excel tables into pivot table lists and I am
looking for a method to do this besides cutting and pasting. The table
has 6 columns (see below) with count of product for each year e.g. xxx1
prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a
4 column list like, (see "Get into pivot table list in this form)

Thanks


Home....Prod....Yr1....Yr2....Yr3....Yr4
xxx1.....prod1....100....200....300....400
xxx2.....prod2....110....210....310....410
xxx1.....prod3....120....220....320....420
xxx2.....prod4....130....230....330....430

Get into pivot table list in this form:

Home....Prod....Yr.......Cnt
xxx1.....prod1...Yr1 100
xxx2.....prod2...Yr1 110
xxx1.....prod1...Yr2 200
xxx2.....prod1...Yr2 210
xxx1.....prod3...Yr2 220
xxx2.....prod4...Yr2 230

Thanks
JB

Real easy trick to that:-

http://j-walk.com/ss/excel/usertips/tip068.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

<[email protected]> wrote in message
news:[email protected]...
> Excel Tables to Pivot Lists
>
> Hello,
>
> I'm trying to convert excel tables into pivot table lists and I am
> looking for a method to do this besides cutting and pasting. The table
> has 6 columns (see below) with count of product for each year e.g. xxx1
> prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a
> 4 column list like, (see "Get into pivot table list in this form)
>
> Thanks
>
>
> Home....Prod....Yr1....Yr2....Yr3....Yr4
> xxx1.....prod1....100....200....300....400
> xxx2.....prod2....110....210....310....410
> xxx1.....prod3....120....220....320....420
> xxx2.....prod4....130....230....330....430
>
> Get into pivot table list in this form:
>
> Home....Prod....Yr.......Cnt
> xxx1.....prod1...Yr1 100
> xxx2.....prod2...Yr1 110
> xxx1.....prod1...Yr2 200
> xxx2.....prod1...Yr2 210
> xxx1.....prod3...Yr2 220
> xxx2.....prod4...Yr2 230
>
> Thanks
> JB
>

Just realised you had two columns at the start of your data, so can still be
done but slightly more complicated:-

ON A COPY OF YOUR DATA:-

Insert a column at the start of your data that concatenates Col1 and Col2
plus a delimiter, eg assuming you added Col A, use

=B2&"["&C2

and copy down as far as needed. Now copy ColA and paste special as values,
delete Cols B and C, and then follow the instructions on John's site.

When done, insert a column between A&B, select Col A, do data / text to
Columns, Delimited, tick 'other' as delimiter and then put [ in the box and
hit Next and Finish.

Regards
Ken......................


"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> Real easy trick to that:-
>
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------*------------------------------*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------*------------------------------*----------------
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Excel Tables to Pivot Lists
>>
>> Hello,
>>
>> I'm trying to convert excel tables into pivot table lists and I am
>> looking for a method to do this besides cutting and pasting. The table
>> has 6 columns (see below) with count of product for each year e.g. xxx1
>> prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a
>> 4 column list like, (see "Get into pivot table list in this form)
>>
>> Thanks
>>
>>
>> Home....Prod....Yr1....Yr2....Yr3....Yr4
>> xxx1.....prod1....100....200....300....400
>> xxx2.....prod2....110....210....310....410
>> xxx1.....prod3....120....220....320....420
>> xxx2.....prod4....130....230....330....430
>>
>> Get into pivot table list in this form:
>>
>> Home....Prod....Yr.......Cnt
>> xxx1.....prod1...Yr1 100
>> xxx2.....prod2...Yr1 110
>> xxx1.....prod1...Yr2 200
>> xxx2.....prod1...Yr2 210
>> xxx1.....prod3...Yr2 220
>> xxx2.....prod4...Yr2 230
>>
>> Thanks
>> JB
>>
>
>