Create a List That is Pivotable
 

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? :confused:


--
Cecil
------------------------------------------------------------------------
Cecil's Profile: http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=563906

Create a List That is Pivotable
 

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 wrote:
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? :confused:




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Create a List That is Pivotable
 

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


--
Cecil
------------------------------------------------------------------------
Cecil's Profile: http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=563906

Create a List That is Pivotable
 

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


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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html