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

nook

New Member
Joined
Apr 2, 2002
Messages
2
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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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;

<PRE>
hammer needles paint bucket
Joe | 1 0 1 1
Eve | 1 1 0 0
Jim | 0 1 1 0
</PRE>

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

<PRE>
Joe | hammer nails paint bucket
Eve | hammer needles
Jim | nails paint needles
</PRE>

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

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top