I have several lists of data from various locations. Each location has a date, time and # of visitors. A snipet of just part of a list of one location is below. Each location has several thousands of records.
Is there a function that will allow me to compare two lists (each from one location) and have the function return the % the two locations are correlated in terms of visitors?
Is there a way I can calculate the ratio of two locations by each date and time?
The problem I am going to have is that some dates and times have no data. This just means the location either had no visitors at that date and time or the number of visitors was the same.
I would like to calculate both scenarios so is there a way to either fill in blanks with 0, or assume 0 when it calculates it uses 0 if one of the two locations being compared doesn't have a record for that date/time? Is there a way to have it use the # of visitors from the most recent date/time?
The lists are in order by date and time
100901 0930 5
100901 1200 16
100902 1430 8
100902 1630 5