Convert Excel Tables to Pivot Table Lists

[jbesr1230]

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

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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......................