Macro to transpose and reformat for upload

[morbdetro]

Right now I have an Excel spreadsheet I am using to plan some purchases. I am trying to figure out a way to take my planning sheet and reformat it so it can be uploaded into my system to auto generate POs. The way the sheet is laid out is that all my future dates are in columns going out 49 weeks. and each row is a separate supplier and part number with the quantities being purchased under the date columns. In order to upload it to my PO system I need one row for each date & quantity.

Essentially I am trying to transpose the entire array but cant just use that formula because I loose visibility on the supplier. I have attached a workbook with some sample data. the "export" tab is how I am receiving the information and the "reformatted" tab is how I need it to be formatted for upload.

[Marc L]

What means 'my system' and 'for upload' to where, what application, and how ?!
Why your planning sheet is not well designed ?

[alansidman]

To normalize your data to the format you wish, unpivot your data with Power Query

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.

[beyond Excel]

Hello.
I leave you a solution that mixes a bit of Power Query and another of macros:

Sub Macro8() 
Range("'Export Format'!A1").CurrentRegion.Name = "myData" 
With Sheets("Reformated").ListObjects(1) 
  .QueryTable.Refresh BackgroundQuery:=False 
  .Range.Columns.AutoFit 
  Names("myData").Delete 
End With 
End Sub  

[sintek]

Please Login or Register  to view this content.

[morbdetro]

Thank you! this worked perfect.

[morbdetro]

Thanks. I have only started learning Power Query. what I have learned so far is great. I'll have to play around with this and see if I can get it to work.

[beyond Excel]

Thanks. I have only started learning Power Query. what I have learned so far is great. I'll have to play around with this and see if I can get it to work.

It's a good decision and thank you very much for the +Rep.