Move columns in downloaded sheet automatically

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect: 2111 is a release number, NOT your actual version. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

I think you will need VBA for this - shall I move the thread for you?

Is it OK to leave the original data? If so, a simple formula will do this...

=IFERROR(LET(a,INDEX(Sheet1!$A$2:$AB$200,,MATCH(A$1,Sheet1!$A$1:$AB$1,0)),FILTER(a,a<>"","")),"")

copied across. In Ukraine... maybe you need ; instead of , (I just don't know!!). See sheet.

Thats why I asked you if it was OK to leave the original data... and you said "yes"

Perhaps using Get & Transform will accomplish what you want.
Here is the Power Query Advanced Editor code used to produce the green/white table on Sheet2:

Please Login or Register  to view this content.

Note that in order to use Get & Transform the data range on Sheet1 will need to be converted to an Excel table (Ctrl + t)
Let us know if you have any questions.

If it can be done "one-click" that would likely require VBA of which I know too little to help.
We may be able to speed up the process by one step if we understand what "Format the new table the way I need it to look like" means.
Do you need the values in the "Numbers" columns left aligned like text?
Is there any other formatting needed other than the fill color of column header cells?

Hey Jete, yes, all formatting I need is left alignment of everything, put whole table with "All borders"; and DATE in format "January/06/2022 12:10:28", NUMBER 6 and 4 in format 123.45 (2 numbers after dot). How do I do that?


UPD. Well, I just figured out how to beatify the table with one click using Table Design and saved preset there So only open question left is what Date and Number type I need to get formats like those above and left alignment.

The following Advanced Editor code produces a text column with the date in "January/06/2022 12:10:28" format.
NUMBER 6 and NUMBER 4 are in decimal number format.

Please Login or Register  to view this content.

The formula of all NUMBER columns could be set to text so that they will left align, however that would make further calculation complicated so in the attached file I chose cell A16 > pressed Ctrl, Shift, right arrow > pressed Ctrl, Shift, down arrow and then selected align left from the Home tab.
Let us know if you have any questions.