+ Reply to Thread
Results 1 to 6 of 6

Large un-uniform dataset to streamline

  1. #1
    Registered User
    Join Date
    10-05-2018
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    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,

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,187

    Re: Large un-uniform dataset to streamline

    How about
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-05-2018
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Re: Large un-uniform dataset to streamline

    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

  4. #4
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,187

    Re: Large un-uniform dataset to streamline

    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)

  5. #5
    Registered User
    Join Date
    10-05-2018
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Re: Large un-uniform dataset to streamline

    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!

  6. #6
    Registered User
    Join Date
    10-05-2018
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Re: Large un-uniform dataset to streamline

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1