Large un-uniform dataset to streamline

Hi all,

So at work I have this large pdf which we convert into a spreadsheet, however it does not do a very nice job:

Capture.PNG

I have done a very rudimentary sketch above. a - g are the column headings and the 1's are the data. The data is misaligned essentially to the headers, however they always have the correct amount of spacing. (This is one of the few issues I face. I can't remember all of the other issues, but I will list them when I get back into office tomorrow)

I did this 6 months ago (as it need to be done bi-annually), and used a lot of formulas including match, index, vlookups etc. However the whole process still took me like nearly a week to finish, not to mention how large and slow the file became.

I am just wondering if there are any clever programs out there/formulas I can use to streamline unorganised data sets?

Thank you,
Regards,

How about

Please Login or Register  to view this content.

Hi,

Thank you for your reply,

Sorry, I am not an expert in excel, would it be possible to explain that code? What does it do and where do I paste that code? Is it some kind of macro?

Thank you

It is a macro & it needs to be copy/pasted into a regular module, have a look here to see how
https://www.contextures.com/xlvba01.html

What it's doing is to delete all the empty cells, so that all data will be in the first 7 columns (for your example)

Omg, thank you, this is amazing and will save me a lot of time!

However as I said before the above was oversimplified, I have a few further issues I need to overcome:

Capture 2.PNG

(Btw, I think you may see here that the values of the figures are one below the actual client, but I can solve with a simple formula)

Black pen = Client name and subsidiary client
Yellow pen = this shows that the Client Payee Total (sum of original client and subsidiaries


1) Essentially, throughout the whole file, there are some clients that have subsidiaries. Some clients can have a large amount of subsidiaries, in the above attachment I have only shown one.

My aim is to remove all the subsidiaries and have the "Payee Total" show in the original line (i.e. the eventual line shows the overall picture of that client). Is there a way to "test the total line against the previous numbers such that it stops at the point when the sum matches the total"? And then replaces that line with the total and removes all subsidiary lines and total?

2)See the red pen in the attachment. There are rogue blank spaces within the data that should not be removed (i.e. the red pen in the pic should be a 0 figure, but the macro removes this and shifts it one space left). I understand that there may not be a fix for this, as there is not discernible identifier to distinguish this from other blanks. But just having a quick look through the data, this rogue blank appears to only appear in the **payee total** line. So I will try to fashion something there.


Thank you,

PS I promise to start learning macros after this, they can be so useful!

EDIT:

For 1), it might be easier actually, for me to use a formula which identifies all of the **payee total** lines, and then take the previous client respectively and sum all the way down to the payee total line. I will have a go,

Thanks