what is best to hold my massive Data

  • Which is best



    I have a massive Excel workbook with 150 work sheets in it containing sensitive data form each of our customer accounts. Each month I filter each work sheet to the current month, update some fields and add some notes, as part of my audit. At the end of the audit, I use a power query to consolidate all the worksheets that have anything to do with the month I was auditing.


    Each of the work sheets was once one massive worksheet that I managed to get excel to split into worksheets based on the customer reference. Some worksheets can have as little as one row and others can have up to 15-20,000 rows


    A customer reference may have many contracts with us, each contract can be 1 to say 10 lines or more


    The contract reference will be the same – the lines were originally free text. There is things like qty, unit and total price and other details like site, post code and my audit columns



    The issue I have is the original data is now over a year old, so what I want is to be able to somehow merge in current data without losing any old data


    I don’t mind if converted to SQL and able to display some sort of form so I can see the details, edit and add in comments


    I need to be able to see data by customer, by month



    Would anyone have any brilliant ideas?


    I would say I am intermediate @ excel but will google and ask for help if I need it


    My SQL is almost non-existent, and not currently installed on my new machine


    I could perhaps show some examples but I can't share the workbook due to the sensative company data

  • Do you have Access available to you?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Than I'd use that to store the data. You can query it from Excel, or use forms/reports in Access to edit/review it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why