Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    .de
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    .de
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •