Hi, i have something that i need help with.
In my spreadsheets, there are two columns with data.
These are Assessed Object and Value of assessed object on Day X.
First off, how would I be able to get the individual objects data to match up on the master sheet without having to look for and select that one particular object? I know there must be a more effecient way where i can pick all objects with their corresponding values and excel sorts them out appropriately. How would I do that???
Due to the fact that objects may be assessed one day and not the next there will be lots of "gaps" in the data. I would like to put a zero value in a blank cell when that happens.
Also, for objects that have appeared for the first time, I need to have values at zero for all previous days upto that day. How would i go about getting excel to automatically do that???
Normally I would copy and paste the data into the master sheet but im dealing with thousands of objects so thats out of the question. Thats why I would like to learn to compile this data more efficiently.
For example,
On day 1- house, car and boat are assessed....so only 1 sheet (day1 sheet) is used and this automatically becomes master sheet as i will record all other subsequent days values onto this sheet.
The next day
On day 2- house, car, and bike are assessed....now there would be 2 sheets (day2 sheet and master sheet). The values for house and car would have 2-days worth of data. But bike, since its a new entry would have an automatic zero for the first days data and would have its day2 data inputted. And since there is no data for boat, it too would have a zero value for day 2. The day2 sheet data would be accumulated with the master sheet. At the end of the day, all data would be found on the master sheet (which now houses day1 and day2 data).
the next day
On day 3- house, boat and farm are assessed....so house would have three days worth of data. boat would have its day 1 data, a zero for day 2 (since it was not assessed on this day), and its day 3 data. Farm, since its a new entry would have a zero for day 1, a zero for day 2, and its day 3 data. car and bike, which were not assessed on this day, would have a zero value for day three. Again, the The day3 sheet data would then be accumulated with the master sheet which contains all accumulated data from previous days (in this case, day1 and day2 data).At the end of the day, all data would now be found on this master sheet (which now houses day1, day2 and day3 data).
On all subsequent days, if there is a new object entry, a zero would need to be automatically entered for all previous days (since the first day of data entry). This is why i made an example with 3 sheets on it, to illustrate this point.
So, in reality, i need a macro that would be able to integrate 1 new sheet a day. So only 1 sheet and a master sheet.
Phew! Im not a quick typer so that took a while, I hope ive made it clear as possible and hopefully this macro I have can be modded??
Thanks <!-- / message -->
In my spreadsheets, there are two columns with data.
These are Assessed Object and Value of assessed object on Day X.
First off, how would I be able to get the individual objects data to match up on the master sheet without having to look for and select that one particular object? I know there must be a more effecient way where i can pick all objects with their corresponding values and excel sorts them out appropriately. How would I do that???
Due to the fact that objects may be assessed one day and not the next there will be lots of "gaps" in the data. I would like to put a zero value in a blank cell when that happens.
Also, for objects that have appeared for the first time, I need to have values at zero for all previous days upto that day. How would i go about getting excel to automatically do that???
Normally I would copy and paste the data into the master sheet but im dealing with thousands of objects so thats out of the question. Thats why I would like to learn to compile this data more efficiently.
For example,
On day 1- house, car and boat are assessed....so only 1 sheet (day1 sheet) is used and this automatically becomes master sheet as i will record all other subsequent days values onto this sheet.
The next day
On day 2- house, car, and bike are assessed....now there would be 2 sheets (day2 sheet and master sheet). The values for house and car would have 2-days worth of data. But bike, since its a new entry would have an automatic zero for the first days data and would have its day2 data inputted. And since there is no data for boat, it too would have a zero value for day 2. The day2 sheet data would be accumulated with the master sheet. At the end of the day, all data would be found on the master sheet (which now houses day1 and day2 data).
the next day
On day 3- house, boat and farm are assessed....so house would have three days worth of data. boat would have its day 1 data, a zero for day 2 (since it was not assessed on this day), and its day 3 data. Farm, since its a new entry would have a zero for day 1, a zero for day 2, and its day 3 data. car and bike, which were not assessed on this day, would have a zero value for day three. Again, the The day3 sheet data would then be accumulated with the master sheet which contains all accumulated data from previous days (in this case, day1 and day2 data).At the end of the day, all data would now be found on this master sheet (which now houses day1, day2 and day3 data).
On all subsequent days, if there is a new object entry, a zero would need to be automatically entered for all previous days (since the first day of data entry). This is why i made an example with 3 sheets on it, to illustrate this point.
So, in reality, i need a macro that would be able to integrate 1 new sheet a day. So only 1 sheet and a master sheet.
Phew! Im not a quick typer so that took a while, I hope ive made it clear as possible and hopefully this macro I have can be modded??
Thanks <!-- / message -->
