Total data that is on seperate rows

russck

New Member
Joined
Oct 22, 2013
Messages
3
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.
ID</SPAN>
Event</SPAN>
Date/Timestamp</SPAN>
123-45-789a</SPAN>
searchbyphone</SPAN>
10/7/2013 12:52:38</SPAN>
987-65-432b</SPAN>
searchbycity</SPAN>
10/7/2013 12:52:39</SPAN>
123-45-789a</SPAN>
searchresult.found</SPAN>
10/7/2013 12:52:41</SPAN>
874-35-123c</SPAN>
searchbyzip</SPAN>
10/7/2013 12:52:42</SPAN>
987-65-432b</SPAN>
searchresult.not found</SPAN>
</SPAN>10/7/2013 12:52:47

<TBODY>
</TBODY>
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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

New Member
Joined
Oct 22, 2013
Messages
3
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

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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

New Member
Joined
Oct 22, 2013
Messages
3
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

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
I would bump the thread. See if someone else can help you. Perhaps with VBA is easier...
 

Forum statistics

Threads
1,143,654
Messages
5,720,101
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top