Auto Consolidation - order based on workbook name

[Anand307]

Hi, We have around 21 -25 workbooks. Data from these workbooks needs to be consolidated to a new workbook. All the workbooks are placed in same folder. All the files have the same format & headers, it has data from column “A” to “S”, however data as per Rows count may vary from one file to another. Data needs to be copied from each workbook starting from cell A2 to Ctrl +Shift +Down arrow+ Right arrow only and paste it in a new workbook starting from cell “A2” one below the other.

However we have couple of rules for this –

1. There is a naming order in which the workbook has to be opened first, that is first open workbook that has name as 609950 - copy and paste the data to a new workbook, then second file with name as 609921. I have provided the list of files names in order it has to be opened.

Consolidate the files in the below order one after the other in a new workbook


609950

609921

601748

609391

600138

601903

601937

601851

604094

103275

103644

609380

103009

103066

100363

100958

304986

601847

602288

602289

602311

Also if there are additions to this list in future it should be amendable in the codes. If a file mentioned above is missing in the folder skip to next file. If in case in any workbook cell “A2” is blank then close the file and go to next file.

2. After consolidation of all the above files, in the new workbook column “F” apply auto filter and filter for contains “FT” and delete rows.

3. Remove filter from column “F” and in column “S” apply filter for Does not contain “NULL” and delete rows. Unfilter. Save.

Unable to share the files, but let me know if you need further instructions.

 

[rrocker1405]

Hi Anand,

Use PowerQuery to consolidate files. Power Query is an add-in from microsoft which you can download from here (32/64-Bit)
https://www.microsoft.com/en-gb/download/details.aspx?id=39379

Follow this kb/blog/videoes to consolidate your files. Using this way all you would have to do is just place the file in the same folder and once done just refresh as a data source. The best part is you dont need to know the indepth of programming to use Power Query.

Step by step guide:
http://www.powerpivotpro.com/2015/0...mbine-multiple-files-of-different-file-types/

Youtube video:

Microsoft KB:
https://support.office.com/en-us/ar...er-Query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d

Hope this helps.

Kind regards,
A!

 

[Anand307]

Hi, both 32 bit and 64 bit files are not getting installed. If possible can you please suggest VBA code to sort this out.

 

[Marc L]

Hi !​

Consolidate the files in the below order one after the other in a new workbook

Also if there are additions to this list in future it should be amendable in the codes.

So the easy maintenance can't be within the code !
Just create the files list order in a parameter worksheet in the code workbook.​

If a file mentioned above is missing in the folder skip to next file. If in case in any workbook cell “A2” is blank then close the file and go to next file.

2. After consolidation of all the above files, in the new workbook column “F” apply auto filter and filter for contains “FT” and delete rows.

3. Remove filter from column “F” and in column “S” apply filter for Does not contain “NULL” and delete rows. Unfilter. Save.

A bit dumb to do it after consolidation !
Better is to copy only needed data workbook by workbook
after a filter or better an advanced filter
(both at beginner level, must see in Excel help and use Macro recorder) …
​