How to Pull data from Un-arranged columns to Arranged columns

Copper Contributor
Hello ,

I have a workbook , where I am suppose to maintain the whole year’s data which has multiple columns. I have arranged all such columns in the required sequels. Like when I get raw data , columns are Z , P , A , D , Q.

Based on my need for the first month , I had arranged these columns into A , D , Q , P , Z and freeze the format. Now whenever I will get a raw file , it will be always in un-arranged way. So now i need your guidance, how to automise the process of importing data from un-arranged columns to arranged one , without using copy and paste function. Also how to add new columns if came.

Also note that I need data in an existing file. To extend the example, when I had freez the format , it has data till 700 row number. So now if i pull the data from un-arranged file to this file , then new data should start with row number 701.

Thanks in Advance.
4 Replies
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), please no picture.
In some cases, the translation does not help to understand the problem. It would be a great help, you don't have to readjust everything to see where the problem could be.

Thank you for your understanding and patience


Nikolino
I know I don't know anything (Socrates)

@Varsit_N_Shah 

Power Query could work. If you have one table with column headers as A, B, C (table could be empty one, only headers) and append to it table with headers as C, A, B, resulting data will be placed as A, B, C

@Sergei Baklan 

Thank you for your prompt and valuable reply. Basically I am preparing a template for my team for filing  monthly tax returns. We will get monthly data sheets only after the end of the month. We get the data sheets with more than 100 columns that too in un-arranged manner ( As client software has different logic for exporting the data into excel ). Even in the data sheet we receive from our client my have new columns or may not have all the columns which we may have in our finalized format.

 

In a current position , my team is to copying data from one column to the desired final columns , and here there is a chance of pasting data under wrong column. So to avoid such error , I want some solution , where my just had to browse the file in to finalized format , data from un-arranged workbook , will be automatically shifted at right column. Any additional column should be added at the last and if any column was not available in a particular month then , in finalized format , for that particular month , that column should remain blank.

To explain this with format , I have attached 4 Files.

1. Finalized Format - My desired output after importing data for 3 months

2. 3 months data files - All the 3 workbooks have un-arranged columns.

I have tried , power query , but may being new to excel ( not pro like you sir ) , i didn't worked for me. As I didn't understood how to give column reference.

 

Once again thanks a bunch for sparing time for me and replying to my queries. Good day. Take care.

 

Varsit N. Shah  

@NikolinoDE @Sergei Baklan 

 

Thank you for your prompt and valuable reply. Basically I am preparing a template for my team for filing  monthly tax returns. We will get monthly data sheets only after the end of the month. We get the data sheets with more than 100 columns that too in un-arranged manner ( As client software has different logic for exporting the data into excel ). Even in the data sheet we receive from our client my have new columns or may not have all the columns which we may have in our finalized format.

 

In a current position , my team is copying data from one column to the desired final columns , and here there is a chance of pasting data under wrong column. So to avoid such error , I want some solution , where my team just had to browse the file in to finalized format , data from un-arranged workbook , will be automatically shifted at right column. Any additional column should be added at the end of the data and if any column was not available in a particular month then , in finalized format , for that particular month , that column should remain blank.

To explain this with format , I have attached 4 Files. 

1. Finalized Format - My desired output after importing data for 3 months

2. 3 months data files - All the 3 workbooks have un-arranged columns.

I have tried , power query , but being new to excel ( not pro like you sir ) , i didn't worked for me. As I didn't understood how to give column reference.

 

Once again thanks a bunch for sparing time for me and replying to my queries. Good day. Take care.

 

Varsit N. Shah