Results 1 to 2 of 2

Thread: VBA - Modify table data in a transpose-y way
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Modify table data in a transpose-y way

    Hi, I have a horrible table with information which was left to me. A bit of the legacy the previous dude left. The table goes like this:

    Order Material SizeA1 SizeA2 SizeB1 SizeB2 SizeC1 SizeC2 Doz1 Doz2
    1234 MAT1 30 31 A AA GM GN 24 63
    4321 MAT2 40 44 XL 4X MN MO 524 636
    5443 MAT3 50 3XL QQ 2516

    It is a table that has some orders of some materials and their sizes. Each order and material can have up to 20 sizes. So, there really should be SizeA1 to SizeA20, SizeB1 to Size B20 and SizeC1 to SizeC20 and Doz1 to Doz20.

    In reality SizeA1 to 20 are the main sizes and SizeB and SizeC are just different versions for different processes. Just when you see different size codes for shoes in different countries but they all mean the same.

    The way this data is ordered makes it to hard to read and work with, so I need a way to modify it so it becomes like this:

    Order Material Size Doz
    1234 MAT1 30 24
    1234 MAT1 31 63
    1234 MAT1 A 24
    1234 MAT1 AA 63
    1234 MAT1 GM 24
    1234 MAT1 GN 63
    4321 MAT2 40 524
    4321 MAT2 44 636
    4321 MAT2 XL 524
    4321 MAT2 4X 636
    4321 MAT2 MN 524
    4321 MAT2 MO 636
    5443 MAT3 50 2516
    5443 MAT3 3XL 2516
    5443 MAT3 QQ 2516


    So instead of having up to 60 sizes distributed in 60 columns I want them in rows with their respective Doz quantity in 20 columns. Just like that, a monster table of 80+ columns can be simplified in 5 columns. In this new table, we could add as many sizes for an order as we want (since you can see they are limited to 20 , right now it is not a problem but we have future plans to increase the amount of sizes permitted and well, things will get really nasty)

    It is worth noticing that not all orders can have all sizes filled. There are are orders, like the 5334 that just have a size in SizeA1, B1 and C1 and thus Doz1 is the only one filled.

    How can I approach this in a VBA way? Or can pivots help me?

  2. #2
    New Member
    Join Date
    Oct 2017
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Modify table data in a transpose-y way

    bump;

Some videos you may like

User Tag List

Tags for this Thread

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
  •