Total data that is on seperate rows

[russck]

Im new here and have been racking my brain for over a week trying many things, researching, and I cant seem to simplify this. I could spend the time and filter, sort and weed out all the information I dont need to eventually get the numbers I am looking for, but I know there is a simpler way.

I have a large amount of data that is from an events file. When I enter it into excel, it almost maxes out the row count (excel 2010)
The data basically lists Event, Date/Timestamp, Call ID, and a few other details. Im trying to get a total of specific events and if they were successful or not, so the other columns arent of concern.
For instance, there is an event called searchbyphone, a later row in the data will include searchresult found or notfound. The only way to pair these up is by the ID number which is unique per call.

So in the example below, ID 123-45-789a was a search by phone and successful (found). The Date/Time field is down to milisecond.
If this were just adding up all the various events and then totalling the searchresult found/notfound, it would be simpler.
However, Im only interested in certain events and found/notfound is used for many other events.

Using the data below, what is the best way to pull out an event (say the total number of searchby phone) and then know how many of those were found and not found (when the information needed is on 2 seperate rows and only the ID would be the same?)

To through a wrench into it, there can be several events with the same ID becasue the ID is unique to a call - a caller can search by a few variables. There will be the same amount of found/notfounds though with that ID. (meaning as a caller, I can do a few searches and all of these will be in the event file with the same ID) In this case, the number of requests (events) will match the number of results (found/not found).

What Im trying to accomplish is to get a total of searchbyphone found and not found, So in this example I would have searchbyphone found = 1 and searchbyphone notfound = 0.
[TABLE="width: 437"]
<TBODY>[TR]
[TD]ID</SPAN>
[/TD]
[TD]Event</SPAN>
[/TD]
[TD]Date/Timestamp</SPAN>
[/TD]
[/TR]
[TR]
[TD]123-45-789a</SPAN>
[/TD]
[TD]searchbyphone</SPAN>
[/TD]
[TD]10/7/2013 12:52:38</SPAN>
[/TD]
[/TR]
[TR]
[TD]987-65-432b</SPAN>
[/TD]
[TD]searchbycity</SPAN>
[/TD]
[TD]10/7/2013 12:52:39</SPAN>
[/TD]
[/TR]
[TR]
[TD]123-45-789a</SPAN>
[/TD]
[TD]searchresult.found</SPAN>
[/TD]
[TD]10/7/2013 12:52:41</SPAN>
[/TD]
[/TR]
[TR]
[TD]874-35-123c</SPAN>
[/TD]
[TD]searchbyzip</SPAN>
[/TD]
[TD]10/7/2013 12:52:42</SPAN>
[/TD]
[/TR]
[TR]
[TD]987-65-432b</SPAN>
[/TD]
[TD]searchresult.not found</SPAN>
[/TD]
[TD]</SPAN>10/7/2013 12:52:47
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[bschwartz]

How do you know whether a searchresult.found record is referring to a specific event? For example, in the above sample, how do you know that there is a count of 1 for Found for 123-45-789a for searchbyphone? Perhaps the .found record is for the same call but a different event?

 

[russck]

How do you know whether a searchresult.found record is referring to a specific event? For example, in the above sample, how do you know that there is a count of 1 for Found for 123-45-789a for searchbyphone? Perhaps the .found record is for the same call but a different event?

By the timestamp. This is where timestamp comes into play. A caller could not initiate more than one event without a response (found/not found)
While trying to accomplish this, Ive already sorted by ID, then by timestamp so its easier to 'see' (pair up) which reponse goes with which event.

To answer your question, that is correct, if just comparing say 4 events and 4 responses with the same ID, you could not determine which response goes with which event. Timestamp is needed for this.

 

[bschwartz]

Sorry, can you clarify a little more how the timestamp links these events? Is it within a certain amount of time (say, 30 seconds) after the first event? Do we expect to find one found and one not found following each searchbyphone? One found OR one not found after each searchbyphone?

What is the pattern?

Thanks.

 

[russck]

Sorry, can you clarify a little more how the timestamp links these events? Is it within a certain amount of time (say, 30 seconds) after the first event? Do we expect to find one found and one not found following each searchbyphone? One found OR one not found after each searchbyphone?

What is the pattern?

Thanks.

There is one respone for an event. So there would be either a searchlocation found or a searchlocation not found.
It isnt in a certain amount of time, although its usually a few seconds at most.

I hope Im not complicating it here. In some instances, a call ID can have several 'search by' options:
Say by city/state, zip, phone, etc. All of these would have a response found not found.
Since I would be interested in only searchby phone, I would only want the appropiate response to that request. The only way to get that is by the timestamp. (the next result in the data for this call ID)
Maybe this isnt a concern because I had filtered out just the event phone and all found/notfounds - Doing this, I need to use the timestamp to get the right response. (since 'filtered' I could see a few found/notfounds with this call ID)



Ideally I would like to place the data in the spreadsheet and pull the event phone and how many found/not founds (by phone) without having to filter, sort etc.

 

[bschwartz]

I would bump the thread. See if someone else can help you. Perhaps with VBA is easier...