copying data only if some criterium is met, and copying it i

[nook]

hello,

I'm having a hard time figuring out the
following problem:

I have customer information (text) in
column A, one per line. And I have product
information (text) in column B, one per
line; a typical excerpt would look like

Joe hammer
Joe nails
Joe paint
Joe bucket
Eve hammer
Eve needles
Jim nails
Jim paint
Jim needles

What I'm trying to achieve is a row - or,
when the alphabet runs out, multiple rows,
based on the data as outlined above:

Joe hammer nails paint bucket
Eve hammer needles
Jim nails paint needles

where every product gets its own cloumn within
the row.

I assume I'll be needing a macro for this,
and that macro would have to check how often
a single customer entry exists in the A
cloumn (indicating how many products
altogether are associated with that customer),
and copy the current value of column B into
the next free column beside the customer
designation.
Also, I believe that for large 'orders', the
Excel approach of A,B,..,Z is not enough; my
current project has some 65k customer/order
pairs, and Joe alone has ordered more than
260 products.. hence I _assume_ that more
than one 'Joe' column will be necessary.

[heh - English is not my native language, and
I doubt I'm making myself understood]

Is this at all possible?

[eliW]

Hi nook,
PIVOTE TABLE is the ultimate solution for these kind of problems!
Put Name in row, Product in Column and Data (count of Product), and your work is done instantly.
Regards,
Eli


[ This Message was edited by: eliW on 2002-04-03 09:43 ]

[nook]

Thanks Eli,
that was a big help - at least now I can
consolidate the products, but it's still
a little more complicated..

As ist is now, I get the total of each
product for each customer;

     hammer needles paint bucket

Joe | 1     0       1     1

Eve | 1     1       0     0

Jim | 0     1       1     0

which takes up a lot of space since there
are so many '0's (remember, the real table
is *way* bigger), and I'd rather not have the
pivot table assistant (wizard, I think, in
the US version) give me the sum totals of
products, but the _name_of the product, as in

Joe | hammer nails   paint   bucket 

Eve | hammer needles 

Jim | nails  paint   needles 

..heh. I'm not making sense.
Anyway, I haven't found a way to tell the
pivot table, err, wizard, to give me the
product names and not to bother giving me
the totals.
I'm sorry to be such a nuisance, but I hope
someone has encountered a similar problem
before, and has found a way to solve it.