+ Reply to Thread
Results 1 to 7 of 7

Transpose Batch Excel Data

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    13

    Transpose Batch Excel Data

    Hello, I have a spreadsheet with rates per lb that I need to transpose into a different format so I can upload into a rating system. I have attached an excel spreadsheet with the original data on the first tab and the desired result on the second tab. I am trying to figure out an easy way to this as the main file has about 1000 rows that need to be transposed. Hope someone can help.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Transpose Batch Excel Data

    With Power Query/Get and Transform which is on the Data Tab. Here is the Mcode resulting from that transformation

    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-05-2016
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Transpose Batch Excel Data

    It absolutely worked. I added the additional data to the file that you sent and refreshed the Query and it populated all the data I needed. Thank you so much. You saved me a ton of time. I do have a question though, where can I see the Mcode in case I wanted to manipulate it by adding more columns to the Source file?

  4. #4
    Registered User
    Join Date
    01-05-2016
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Transpose Batch Excel Data

    It absolutely worked. I added the additional data to the file that you sent and refreshed the Query and it populated all the data I needed. Thank you so much. You saved me a ton of time. I do have a question though, where can I see the Mcode in case I wanted to manipulate it by adding more columns to the Source file?

  5. #5
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,703

    Re: Transpose Batch Excel Data

    Just in case you want a different option, here is a code process:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Transpose Batch Excel Data

    With Power Query open in the editor, there are two places you can see the Mcode.
    1. On Home, click on the Advanced Editor
    2. On View Tab, click on Advanced Editor

  7. #7
    Registered User
    Join Date
    01-05-2016
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Transpose Batch Excel Data

    Quote Originally Posted by maniacb View Post
    Just in case you want a different option, here is a code process:

    Please Login or Register  to view this content.
    Thank you. This does provide me with another option as I have to do a bunch of these every month. I appreciate you taking the time to respond!

+ 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. Batch Transpose
    By probuddha in forum Excel General
    Replies: 3
    Last Post: 01-12-2017, 08:05 AM
  2. [SOLVED] Aggregate Batch Data from many word docs into an Excel (i.e.Check Boxes, Text Form Fields)
    By Chrisdudley7 in forum Word Formatting & General
    Replies: 7
    Last Post: 05-13-2014, 01:28 PM
  3. [SOLVED] Formula to round down on a batch and exclude anything under a full batch
    By toomuchbrew in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2012, 11:04 AM
  4. Replies: 0
    Last Post: 06-19-2012, 11:36 AM
  5. [SOLVED] Batch extract PDF data to Excel using regular expressions
    By dwilkie@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-25-2006, 05:15 AM
  6. [SOLVED] How to batch create / generate and print invoices from Excel data?
    By helenerz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2005, 10:05 PM
  7. Excel charts data auto range / batch update ?
    By agb in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-22-2005, 10:44 AM

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