I have a data set consisting of two columns: 1) calendar dates and 2) a binary coded event (it's coded as 1 or 2, but could just as easily be 0 and 1). The events alternate in blocks of dates. For example:
1/1/10 1
1/2/10 1
1/3/10 1
1/4/10 2
1/5/10 2
1/6/10 1
I need to calculate the length (number of days) of each block as well as the start and end date for each block as well as the corresponding event code value.
Any suggestions would be greatly appreciated.
And in your example, the answers would be _____ because _______?
Entia non sunt multiplicanda sine necessitate
I don't understand your question.
What is the expected result for your example?
Oh...sorry.
It would be:
Event #Days Dates
1 3 1/1/10-1/3/10
2 2 1/4/10-1/5/10
1 1 1/6/10-1/6/10
If you use sequential numbers for the events,
F2: =INDEX($A$2:$A$7, MATCH($D2, $B$2:$B$7, 0)) G2: =INDEX($A$2:$A$7, MATCH($D2, $B$2:$B$7, 1))
I can't use sequential numbers for the events. There's about 10,000 lines for each of about 100 files that are in this format.
If they just go back and forth, 1-2-1-2-..., you could use a helper column to create sequential numbers.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks