+ Reply to Thread
Results 1 to 7 of 7

Transpose data and add rows if necessary

  1. #1
    Registered User
    Join Date
    08-05-2022
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    4

    Transpose data and add rows if necessary

    Hi all,

    I have the following data which shows multiple company's purchased qty by item.
    Screenshot 2022-08-06 093413.png

    Here is my desired output.
    Screenshot 2022-08-06 093612.png

    I have tried using pivot tables and formulas but they all doesn't solve the problem as I need to repeat the item rows if there are multiple purchase from different companies.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Transpose data and add rows if necessary

    Hi & welcome to the forum,

    Try below code, I have also attached a sample file for you ... In case you don't want to delete rows with 0 values, you may delete/comment the part highlighted in blue in the code ...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Registered User
    Join Date
    08-05-2022
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose data and add rows if necessary

    Quote Originally Posted by nankw83 View Post
    Hi & welcome to the forum,

    Try below code, I have also attached a sample file for you ... In case you don't want to delete rows with 0 values, you may delete/comment the part highlighted in blue in the code ...
    Please Login or Register  to view this content.
    Great! I think it works. However, I don't really understand how it works, especially the for loop part. Would you be able to explain? Thank you!

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Transpose data and add rows if necessary

    An alternative is to unpivot your data in Power Query

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Item Co 1 Co2 Co3 Co4 Co5 Co6 Item Attribute Value
    2
    Item1`
    1
    0
    2
    0
    0
    0
    Item1` Co 1
    1
    3
    Item2
    0
    0
    0
    0
    0
    0
    Item1` Co3
    2
    4
    Item3
    0
    0
    0
    0
    0
    0
    Item5 Co3
    3
    5
    item4
    0
    0
    0
    0
    0
    0
    Item7 Co 1
    3
    6
    Item5
    0
    0
    3
    0
    0
    0
    7
    Item6
    0
    0
    0
    0
    0
    0
    8
    Item7
    3
    0
    0
    0
    0
    0
    9
    Item8
    0
    0
    0
    0
    0
    0
    Sheet: Sheet1

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Transpose data and add rows if necessary

    Quote Originally Posted by terminator1 View Post
    I don't really understand how it works, especially the for loop part. Would you be able to explain?
    Hi terminator, I have added description for each line of code. Hope that helps & thanks for the Rep+

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-05-2022
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose data and add rows if necessary

    Quote Originally Posted by nankw83 View Post
    Hi terminator, I have added description for each line of code. Hope that helps & thanks for the Rep+

    Please Login or Register  to view this content.
    Thanks for the explanation.

    Now, I would just want to add another column to the far left for the Model Category in my original data. So every Model will have a Model Category linked to it.

    In the output, it should be Model Category|Model|Company|Qty . How do I amend the code?

  7. #7
    Registered User
    Join Date
    08-05-2022
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    4

    Re: Transpose data and add rows if necessary

    I think I have managed to amend the code for my above requirement...

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-30-2020, 10:08 AM
  2. Transpose rows to column and insert new rows to fill in data
    By Apple Ling in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2019, 04:02 AM
  3. Replies: 7
    Last Post: 10-03-2014, 05:04 AM
  4. Insert rows, transpose data from column in to rows, in steps
    By mchevalier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2012, 08:14 PM
  5. Transpose Data to rows
    By bigroo1958 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2011, 08:17 PM
  6. transpose many rows of data
    By tania_del in forum Excel General
    Replies: 3
    Last Post: 05-22-2007, 03:51 PM
  7. Replies: 2
    Last Post: 02-19-2007, 04:53 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1