Create a List That is Pivotable

[Cecil]

I have a worksheet of rows of customers and columns of products. There
are about 250 customers and about 100 products. Therefore, there are
25,000 cells of data. Each cell contains the dollar amount of that
product sold to that customer. I would like to create a 3-column list
of data that contains customer-name, product-name and dollars sold.
The number of rows in that list would equal the number of cells that
are non-zero. In other words, cells with zero dollars can be ignored.
How can this be accomplished?

 

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

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

After you create the list, sort by value, and delete all the rows with
blanks, at the end of the sorted table.

 

[Cecil]

Thanks, Debra. That's the "perfect" solution. Hey, I just bought you
book "Excel Pivot Tables Recipe Book" --- lottsa good info, well wort
the $$$. I live in the Toronto area --- do you offer seminars o
anything like that? Thanks, Cecil

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that you like the book. I
don't offer seminars, but there are tutorials on my web site:

http://www.contextures.com/tiptech.html