Excel Pros! I need your help!

Hey guys, let me explain what I need help with.

Let's pretend I have data in columns A b c and d.

Column C is full of timestamps.

How would I remove "similar" timestamps. As in within 2-3 minutes of each other?

Conditional formatting? I'm not sure how to approach this.

Google has failed me

 

When I say remove similar timestamps, I would like to remove those rows themselves.

 

Attach a "sample" excel file of what you're working with. It may make more sense... at least to me.

Frank

 

Sample attached.

 

Is this excel receiving data from an outside program? Or is the data being entered manualy?

Frank

 

Entered manually. Via copy and paste.

 

I guess I don't get it. You have a ton of transactions on a spreadsheet, and you want to separate it by customer... using the timestamp? Whut?

For the record, I am no Excel expert... I've probably only scratched the surface of Excel knowledge, but I've gotten pretty good at it working at a large enterprise...

 

I am trying to find unique customers, based on timestamp.

Basically I just need to delete one of the rows within 2 minutes of the other.

 

the most robust way would be to do your forumulas to parse the timestamp in terms of true "absolute" time (i.e. factor in the date and the time)... an easier (but possibly more problematic) way would be to just parse the time value from the timestamp, assuming you can live w/ the ambiguity from leaving out the day (i.e. what if two or more timevalues from different days are present)..

for the latter i will show a simple formula to parse the time portion from cell C1
"=MID(C1, FIND("2012 ",C1,1)+5,1000)" this parses it...

...then apply this forumla to convert the time portion from text string into a number you can work w/ :

"=VALUE(E1)*24*60" ....note, you can nest these two formulas if you want...

...the value forumula converts a text-based time value into an absolute numeric value, ..based on fractional days, ...so these conversion factors to get it in terms of "absolute" minutes 24 hours/day * 60 minutes/hour...

when you have this you can sort the array, and then do forumulas to to see if contents are w/in some proximity value to others...

now, ..the former part (worrying about the ambiguity from the date portion - i'll leave to you! )

...

 

Yeah..what wileyC said!

 

I would need to take into consideration both date and time. Thanks for the great response.

 

Nerds be scary.

 

sure thing... i've done quite a bit of stuff w/ excel, ..it's quite powerful and a handy problem-solving tool, oftentimes better than writing a computer program or some such thing... let me know if i can provide more help..

 

How would I parse to get a number for "May 31 2012 3:59:46 PM"?

 

I'm not sure, but I know you can format to date/time. If that could be used in a formula so that you could calculate it, it should be pretty easy to write a macro(for somebody that knows how.) that does that for you. I am currently looking for some help on writing a macro to protect the sheets upon closing. I am on the mrexcel forum looking for some answers on that. I would think some of those guys would have something hammered out for you pretty quick as well. Seems like some sharp guys that do this stuff. I, unfortuantely, am not one of them.....

 

I will take a look over on mrexcel.

It is very strange, I am able to convert other mm/dd/y to a number but not this one with the word May in it...

 

Btw I have just purchased "Excel for Dummies" on Amazon.

 

I know you can conditional format it so that if it is between numbers the text goes the same as the block it is in, essentially hiding it. You wouldnt be able to count it, since there is actually still a value there.....

You COULD however add another column beside the timestamps with an IF statement saying that for example =IF(A3>=A2+5, New Customer,0) With this, if the second timestamp was more than 5(this is arbitrary and I dont know how time is formatted for calculations to be able to say "take off this much for this many minutes") differant, it would populate New customer and if it was before the time period, it would simply enter a 0. As far as deleteing the rows, that will have to be a macro written in VBA. You wont be able to record that. This way you could however atleast calculate how many new customers you have by a count formula.