Large Daily data set with missing measurements

[malexoran]

Hi, I'm Alex. I'm new to using Excel for data analysis and might have a few requests for help over the coming months.

My first problem is just in regards to getting started:
I have to analyze a large data set whereby measurements were supposed to have been taken every 30 seconds every day for a year leaving me with over a million cells of data. Unfortunately the readings haven't been entirely reliable and several times a day there is a measurement missing. The way the system presents the information is as follows, in column A we have our time and date entry in this format "12/31/2015 5:58:50 PM", in the cell below we have "12/31/2015 5:58:20 PM", these time entries continue in this fashion in the same column the whole way back to "01/01/2015 00:00:20 AM", a million cells below our first entry.
Unfortunately, when there is an entry missing there is no empty space or anything, the time time just changes by 1 minute instead of 30 seconds when you move into the cell below. All the corresponding measurements are in the adjacent column B.
What I need to do is divide the data into daily columns with an equal number of measurements, is there a way that I can achieve this given the time and date format? If this is possible, I will then need some guidance on how to best split my single column of measurements into 365 daily columns of measurements.

I have just had another look at one of my data sets and I have seen that at times there are larger time scales covering several hours that are missing. I will also have to find a way to get Excel to let me know how much time is the system has not been running over the year.

Any help on this would be greatly appreciated as I am at a loss for now.

Thanks

[FDibbins]

Hi, welcome to the forum

With a million+ entries, this is probably going to be resource-intense and may take some time to process.

Can you show upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[malexoran]

Hey Fdibbins,
Thanks for your reply. I have a sample of the data for you but the forum doesn't seem to want to let me attach the file, when I click the icon I just get a blank bar. I have tried on other browsers with the same results. Any suggestions? If you PM me your email I can send the sample to you ???
Thanks for again for getting in touch.

[JeteMc]

To attach a sample workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window. The paper clip icon seems to be disabled at least for the time being.
Let me know if you have any questions.

[malexoran]

Hi, I think I've got that sample properly attached. The format I need it in is similar to the format it is in now only that I need to know where the missing readings are and assign the missing time periods an "unknown
" value.
I have a number of spreadsheets with different parameters that have been measured the same number of times over the same time period. The eventual goal is to have all the parameters from the different spreadsheets collated onto the same sheet in adjacent columns for easy analysis with the time being the first column.

[JeteMc]

Attached is a file that compares the times at which measurements were recorded to the times they should have been recorded based on the criteria given in post #1. One of the issues is that the times have to be synced which this solution accomplishes by utilizing a helper column C with the formula that rounds to the nearest 10 seconds:

Please Login or Register  to view this content.

Column D counts back from 12/31/2015 23:58:50 to 12/31/2015 00:00:20 and column D looks up the measurements from column B based on synced times in columns C and D with the formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

[malexoran]

Hey, Thanks for the input. I think I understand what's going on here.
Thanks again, your help will surely prove to be most useful.

[JeteMc]

You're Welcome and thank you for the feedback. If that takes care of your original question, please select Thread Tools from the menu link above your first post and mark this thread as SOLVED. I hope that you have a good day.

[malexoran]

Hey JeteMc, it seems like I'm not quite there yet. When I used the formula the first half million rows seemed to provide accurate empty cells where values had not been assigned in my data. although it looked like the formula worked throughout the sheet, after deeper inspection it didn't seem to work exactly right as I began to find cells that didn't correspond to my info. When I saved the sheet, the formula made the program crash, this happened on several occasions and every time I would reload the worksheet I would be advised "There have been no errors detected on this file" or something along those lines, then once I tried to do something on the sheet, excel would crash again. So, unfortunately I have had to remove the "IFerror" function. Any suggestions???

[JeteMc]

With the ifferror function removed #N/A is returned when there is not a match. Since the object is to have blank spaces in those cells where there was no data for a particular time slot I'd suggest deleting the #N/A from those cells by selecting the whole range then from the home tab select Find Select > Go To Special > Formulas > Errors (deselect the other three formula options) > OK then delete. As for the formula returning values when none exist, we would need to see a copy of that portion of the data and I'll be out for most of the day, so it might be best to start a new thread and upload a sample file that displays that unwanted behavior.

[JeteMc]

although it looked like the formula worked throughout the sheet, after deeper inspection it didn't seem to work exactly right as I began to find cells that didn't correspond to my info.

Are there any instances of this in the file attached to post #6, and if not could you attach a sample from the data where this occurred? Please be sure to mark the cells that don't correspond and/or show the values you would expect the formula to return.

[malexoran]

Hey, thanks for keeping an eye on the thread. Because getting these blank cells is only the first of many manipulation that I have to do with this data, and this has proved to be a very time consuming task, I've asked someone with the required skills to lend a hand. I think they're going to write some Python scripts the help me get through it. Should I marked this thread as resolved?
Thanks again for your help jetemc, sorry I couldn't do more with it.

[JeteMc]

I think that marking the thread 'Solved' would be appropriate as the project has moved from Excel to Python. I hope that you have a good day.