Mass Data Management

[wardja19]

Hi, i currently manage a large dataset for a dashboard which requires values such as; cash at bank and total debtors to be updated daily. As i want to keep all my past data so i can show a trend i current have a large data set that has all the dates for the next year and i have a 'Vlookup' function up the top to obtain the current date and all the values attached.

I am curious if there is a way to enter the date at the top and whatever daily data next to it. And this data automatically getting added to my large data set. Then on the next day it resets due to the date being updated but the data in the table remains.

My current work around is a conditional format that highlights the current cell that needs to be entered based on the date.

I have not yet required macros in the worksheet and am trying to avoid them but if it is necessary i will apply.

 

[pecoflyer]

Hi
please post a sample sheet with some data and expected results

 

[Chihiro]

If you have access to it, I'd recommend leveraging PowerQuery and/or PowerPivot. These tools are meant to handle relatively large data set and has very efficient compression method (handles up to several mil rows comfortably, and can be stretched for more if needed).

If anything larger is needed, I'd go with other tools such as PowerBI combined with true relational DB.

 

[wardja19]

Here is the sample file, i have comments in the file which state the same as my earlier post. I haven't yet used PowerQuery, if what i am after is manageable through could someone explain to me how to set it up?