Mike "Wuzzy121". Rest in peace, brother

Go Back   Tacoma World Forums > Off-Topic > Technology

Notices

Excel Pros! I need your help!

Reply
 
Thread Tools Search this Thread
Old 05-11-2012, 04:14 PM   #1
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
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
  Reply With Quote
Old 05-11-2012, 04:19 PM   #2
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
When I say remove similar timestamps, I would like to remove those rows themselves.
  Reply With Quote
Old 05-11-2012, 04:25 PM   #3
UBIQUE
Highland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shed
 
Highland Logan's Avatar
Canadian Army: Royal Canadian Artillery (Sgt, Ret.)
Name: Frank
Joined: Apr 2011, #54752
Location: Nova Scotia, Canada
Gender: Male
Posts: 2,754
Highland Logan's Tacoma Gallery
Attach a "sample" excel file of what you're working with. It may make more sense... at least to me.

Frank
  Reply With Quote
Old 05-11-2012, 04:49 PM   #4
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
Sample attached.
Attached Files
File Type: xls sample.xls (31.0 KB, 67 views)
  Reply With Quote
Old 05-11-2012, 04:55 PM   #5
UBIQUE
Highland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shedHighland Logan is one of the sharper tools in the shed
 
Highland Logan's Avatar
Canadian Army: Royal Canadian Artillery (Sgt, Ret.)
Name: Frank
Joined: Apr 2011, #54752
Location: Nova Scotia, Canada
Gender: Male
Posts: 2,754
Highland Logan's Tacoma Gallery
Is this excel receiving data from an outside program? Or is the data being entered manualy?

Frank
  Reply With Quote
Old 05-11-2012, 05:12 PM   #6
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
Entered manually. Via copy and paste.
  Reply With Quote
Old 05-11-2012, 05:19 PM   #7
Banned
arrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shedarrrghhh is one of the sharper tools in the shed
Joined: Jan 2009, #12748
Location: Denver, CO
Gender: Male
Posts: 2,076
arrrghhh's Tacoma Gallery
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...
  Reply With Quote
Old 05-11-2012, 05:22 PM   #8
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
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.
  Reply With Quote
Old 05-11-2012, 09:13 PM   #9
Senior Member
wileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shed
 
wileyC's Avatar
Joined: Oct 2011, #65685
Location: Centerville, OH
Gender: Male
Posts: 2,767
wileyC's Tacoma Gallery
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! )

...
  Reply With Quote
Old 05-17-2012, 12:03 PM   #11
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
Quote:
Originally Posted by wileyC View Post
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! )

...
I would need to take into consideration both date and time. Thanks for the great response.
  Reply With Quote
Old 05-17-2012, 12:07 PM   #12
Senior Member
MountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shedMountainEarth is one of the sharper tools in the shed
Name: Bryan
Joined: Feb 2010, #30519
Location: CO
Age: 43
Gender: Male
Posts: 2,479
MountainEarth's Tacoma Gallery
Quote:
Originally Posted by wileyC View Post
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! )

...
Nerds be scary.
  Reply With Quote
Old 05-17-2012, 08:47 PM   #13
Senior Member
wileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shed
 
wileyC's Avatar
Joined: Oct 2011, #65685
Location: Centerville, OH
Gender: Male
Posts: 2,767
wileyC's Tacoma Gallery
Quote:
Originally Posted by Blasphemous View Post
I would need to take into consideration both date and time. Thanks for the great response.
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..
  Reply With Quote
Old 05-17-2012, 08:50 PM   #14
Senior Member
wileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shedwileyC is one of the sharper tools in the shed
 
wileyC's Avatar
Joined: Oct 2011, #65685
Location: Centerville, OH
Gender: Male
Posts: 2,767
wileyC's Tacoma Gallery
Quote:
Originally Posted by MountainEarth View Post
Nerds be scary.

  Reply With Quote
Old 05-31-2012, 04:28 PM   #15
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
How would I parse to get a number for "May 31 2012 3:59:46 PM"?
  Reply With Quote
Old 05-31-2012, 04:33 PM   #16
Gone But Not Forgotten
ImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shedImpulseRed008 is one of the sharper tools in the shed
 
ImpulseRed008's Avatar
Air Force:
Name: Susan
Joined: Sep 2008, #9104
Location: SC
Gender: Girl
Posts: 39,450
ImpulseRed008's Tacoma Gallery
Quote:
Originally Posted by Blasphemous View Post
How would I parse to get a number for "May 31 2012 3:59:46 PM"?
  Reply With Quote
Old 05-31-2012, 04:51 PM   #17
Senior Member
Country101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to all
Joined: Apr 2012, #76429
Location: NW Arkansas
Gender: Male
Posts: 443
Country101's Tacoma Gallery
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.....
  Reply With Quote
Old 05-31-2012, 04:59 PM   #18
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
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...
  Reply With Quote
Old 05-31-2012, 04:59 PM   #19
Senior Member
Blasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shedBlasphemous is one of the sharper tools in the shed
Name: Mike
Joined: Apr 2011, #54632
Location: San Fernando Valley, CA
Gender: Male
Posts: 630
Blasphemous's Tacoma Gallery
Btw I have just purchased "Excel for Dummies" on amazon.
  Reply With Quote
Old 06-01-2012, 11:09 AM   #20
Senior Member
Country101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to allCountry101 is a name known to all
Joined: Apr 2012, #76429
Location: NW Arkansas
Gender: Male
Posts: 443
Country101's Tacoma Gallery
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.
  Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Experts out there? memario1214 Technology 25 11-08-2011 09:39 AM
iPad and Excel Mitch Technology 6 08-22-2011 10:24 AM
Help With Excel jdkeller Technology 8 01-10-2011 05:57 AM
Excel Gurus...Need your help... solus Technology 3 12-02-2010 09:00 PM
Help With Excel Spreadsheets AFButters Technology 4 05-20-2010 09:41 AM


Copyright © 2014 Tacoma Forum. Tacoma World is not owned by, or affiliated with Toyota Motor Corporation.