Hi Catalin

OK, attached two sample test files with data descriptions modified for privacy...name is test file1.xlsm, with data for 2007. All my actual files(20) have data similar to this with original descriptions in the Description column of course.Also attached a modified file as test file2.xlsm that also runs Phil's code.

As you will see in test file1.xlsm, there are hidden BLANK rows, merged cells and data starts in Column B Row 5, headers in Row4. So if I run Phil's macro code, I get zip in the MergedData worksheet (as expected!)

I then used a 2011 file,modified the two test worksheets, Page1 and Page2 (attached as test file2.xlsm), and ran Phil's code again and it worked OK, giving the MergedData worksheet, since there were no blank rows and data started in ColumnA.

I found that since the data in Columns A, C and D are in General format, sorting by date results in all days for each month being listed together and of course I cannot create a running balance. I then created formulas in Columns G,H and I to compensate for data in Columns C and D to fix this up and then a Calculated Balance in column J to duplicate the results in Column D. I then copied this worksheet as Merged Data(2) and deleted all the info rows and just copied and pasted as values with formatting back into columns G,H,I and J...the date sort also now works.

Can the final result in the MergedData(2) worksheet be done completely in VBA without me having to modify each and every worksheet in every file...I am not sure of how to code for this. The main issue of course is that it would be very, very tedious to modify each and every worksheet in every file since I have approx 20 files with up to 27 to 30 worksheets in each file. So how can I modify Phil's code to work on the original test file.xlsx to give a result similar to that in the Merged Data (2) worksheet in the test file2.xlsm?

Please help if possible as this is driving me nuts!

Thanks

Russ

Hi Russ

You could consider using a 3rd party utility to merge the files. No need to reinvent the wheel by writing codes.

Once you have merged them, you can then easily clean them up manually. It beats cleaning every worksheet in each file manually before merging 

One utility that is quite good is the RDBMerge Add-In and its FREE. It can be found here  http://www.rondebruin.nl/win/addins/rdbmerge.htm

I have tested it on your files. The actual output (before manually "cleaning") is attached  - Merged Data Using Utility.xlsx

I then manually cleaned the merged data and it took me just a couple of minutes - Merge Data After Cleaning.xlsx.

Hope this helps.

Sunny

Hi Russ,

If you have Excel 2010 then you can use Power Query (free Microsoft add-in) to get the data out of your files and into the format you want. In the attached file I've used your test-file1.xlsm to merge the data in Page 1 & Page 2.

This can be adapted to get the data from your 20 separate files and merge it in one go. The format of the data in those 20 files would have to be the same, but if your test-file1.xlsm is anything to go on then they will be.

Download Power Query: https://www.microsoft.com/en-au/download/details.aspx?id=39379

Note, if you have Excel 2016 then you don't need the add-in as it's built into Excel. You'll find it on the Data tab in the 'Get & Transform' group of tools.

Mynda

Hi Mynda

OK,thanks, yes I do have Power Query, just haven't used it for a long time but I will give it a go.

However, with the sample you attached, in the merged file, it shows the item for COLES GISBORNE VIC AU with a value of $59.90 and a date of 8/06/2011. So what has gone wrong, since the COLES entry on Page2 has a date of 4/06/2011 and no value in the Debits/Credits column and Balance column. I assume the DEBITS & CREDITS columns are arrived at by Grouping. Also, info (17/06/2011)which showed  a blank in the Debits/Credits and Balance columns does not appear on the merged sheet for 17/06/2011 from Page1 and Page2, likewise for any info items that have blanks in the Debit/Credit and Balance columns.

I also did do a running balance in a new column using a simple formula of Balance=Balance-credit but when I apply this, the calculated balance column starts to lose correlation with the exiting balance column as I go down the column? The blue surrounded areas are correlated but then it drops out, comes good again, drops out but inexplicably comes good at the bottom. What gives?.

This is sending me round the bend!

Thanks

Russ

🙂 sorry, Russ, I forgot to tell you that I changed one of your Credits to a Debit because the sample file '1' only had Credits, but your second sample file also included debits.

At a glance it looks like the 'Info' lines relate to the actual transaction lines above, so I filled those descriptions up, hence the Coles Gisborne note on the wrong line. So, if the Info lines don't relate to the actual transactions above, what do they relate to?

To be able to automate the cleaning of this data (in Power Query or with VBA) we need rules that are consistent and looking at your file more closely it doesn't look like this is the case. e.g. what is that Coles Gisborne transaction? There's no value for it and it's on a different date to everything else.

Of course, if you wanted to keep those lines interspersed as they are in your test-file2 on the MergedData sheet, you could, but this would mean it's not in a proper tabular format and so the data isn't as useful as it could be. e.g. you couldn't use it in a PivotTable. The next best option is to remove the Info lines as you did in your MergedData (2) example. So many options, I didn't know which one you wanted 🙂

Anyhow, I created another file using Power Query (attached here) and in that I removed the 'info' and used a PivotTable to automatically summarise the data and insert a running balance using a calculated field. Of course it doesn't have the opening balance information as that's not in the data set, so you could always just keep the 'running balance' column from the source data instead of calculating it.

Mynda