MACRO to group bank statements by transaction.... HELP!

[lozoroo]

Dear Excel Experts.

I'm desperately in need of some assistance before my tiny little head explodes!

Essentially I am trying to sort a number of bank statements which have been scanned through OCR onto excel. The data for each transaction is split over (in most cases) several rows.

I am looking to combine (rather than merge) all rows relating to individual transactions so that each row contains data for a unique transaction.

This is quite a tricky conundrum to explain, so hopefully the attached will clarify my issue.... (please see the xlsx file; I uploaded the other file by mistake, and can't for the life of me delete it!)

As you will see from Sheet 'OCR,' the data I have is not aligned and quite difficult to interpret exactly what information belongs with which... the date for each transaction is actually held in the last row of applicable data, rather than the first. If you look on Sheet 'Grouped Transactions' I have marked the cells which actually belong together as relating to the same transaction. On Sheet 'Merged Transactions' you will see how I intended the data to look.

I did originally consider using a different column (rather than date) to dictate which rows belonged with which, however the date entry is the only constant in the data I have - not all transactions have data in each column, hence this turned out to be a bit of a catastrophe.

I'm desperate for a macro to help me sort this. I have about 2000 rows in total in my original.

I think I could run another macro which would solve this, if only I could get the date to appear at the beginning, rather than end, of each transaction detail.

Please, please help!

[royUK]

Why not download statements as csv files?

How's Nottingham by the way?

[lozoroo]

Why not download statements as csv files?

How's Nottingham by the way?

Doesn't work I'm afraid, plus I'll be needing to do this on a regular basis, and don't have personal access to the OCR.

Nottingham... cloudy, overcast, with a slight teasing of sun.

Imagine Derby is the same

[jaslake]

Hi lozoroo

I only picked up on this 'cause it looked like an interesting exercise...well, it quickly became convoluted as is the code in the attached. The code does work on your sample .xlsx file. However, I can't believe it'll work in the real world...too many unknowns to program for. You're welcome to try the code on a copy of your real file...let me know how it goes.