1. Welcome to Tacoma World!

    You are currently viewing as a guest! To get full-access, you need to register for a FREE account.

    As a registered member, you’ll be able to:
    • Participate in all Tacoma discussion topics
    • Communicate privately with other Tacoma owners from around the world
    • Post your own photos in our Members Gallery
    • Access all special features of the site

Excel Pros! I need your help!

Discussion in 'Technology' started by Blasphemous, May 11, 2012.

  1. May 11, 2012 at 4:14 PM
    #1
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    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 :(
     
  2. May 11, 2012 at 4:19 PM
    #2
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    When I say remove similar timestamps, I would like to remove those rows themselves.
     
  3. May 11, 2012 at 4:25 PM
    #3
    Highland Logan

    Highland Logan UBIQUE

    Joined:
    Apr 11, 2011
    Member:
    #54752
    Messages:
    2,812
    Gender:
    Male
    First Name:
    Frank
    Nova Scotia, Canada
    Vehicle:
    2011 Black AC TRD Off Road w/ factory tow pkg
    Attach a "sample" excel file of what you're working with. It may make more sense... at least to me.

    Frank
     
  4. May 11, 2012 at 4:49 PM
    #4
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    Sample attached.
     

    Attached Files:

  5. May 11, 2012 at 4:55 PM
    #5
    Highland Logan

    Highland Logan UBIQUE

    Joined:
    Apr 11, 2011
    Member:
    #54752
    Messages:
    2,812
    Gender:
    Male
    First Name:
    Frank
    Nova Scotia, Canada
    Vehicle:
    2011 Black AC TRD Off Road w/ factory tow pkg
    Is this excel receiving data from an outside program? Or is the data being entered manualy?

    Frank
     
  6. May 11, 2012 at 5:12 PM
    #6
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    Entered manually. Via copy and paste.
     
  7. May 11, 2012 at 5:19 PM
    #7
    arrrghhh

    arrrghhh Well-Known Member

    Joined:
    Jan 19, 2009
    Member:
    #12748
    Messages:
    2,072
    Gender:
    Male
    Denver, CO
    Vehicle:
    09 TRD Off-Road
    Custom Front Bumper, Smittybilt XRC-8, 3" OME lift, 33" Falken A/T tires
    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...
     
  8. May 11, 2012 at 5:22 PM
    #8
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    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.
     
  9. May 11, 2012 at 9:13 PM
    #9
    wileyC

    wileyC Well-Known Member

    Joined:
    Oct 22, 2011
    Member:
    #65685
    Messages:
    2,924
    Gender:
    Male
    Centerville, OH
    Vehicle:
    2012 PreRunner Double Cab TRD Sport
    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! ;))

    ...
     
  10. May 12, 2012 at 8:16 AM
    #10
    khx73

    khx73 Well-Known Member

    Joined:
    Sep 22, 2011
    Member:
    #63950
    Messages:
    609
    Gender:
    Male
    Saint John NB, CAN
    Vehicle:
    11 MGM DCLB TRD Sport +leather
    Rear camera anytime switch, 4x4 Illuminated Switch (from rcbs204) , rear view mirror lift bracket (from ImMrYo), 75 cent tailgate anti-theft. Technically not mods but give me a break I'm trying to fill space here... OEM Leather pkg, keyless unlock & ACC chime silence, BakFlip G2, Ultra Gauge EM, WeatherTech floor mats, OEM roof rack
    :eek:

    Yeah..what wileyC said!
    :anonymous:
     
  11. May 17, 2012 at 12:03 PM
    #11
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    I would need to take into consideration both date and time. Thanks for the great response.
     
  12. May 17, 2012 at 12:07 PM
    #12
    MountainEarth

    MountainEarth Well-Known Member

    Joined:
    Feb 1, 2010
    Member:
    #30519
    Messages:
    2,495
    Gender:
    Male
    First Name:
    Bryan
    CO
    Vehicle:
    2010 TRD OR Access
    Leer 100XR Shell, BedRug mat - comfy sleeping, GT Covers microfiber seat covers, BFG All Terrains 265/70r16, Dashmat, Antennax 13" shorty antenna, Weathertech liners, Ultra Gauge, Avid Light Bar, PIAA 520 ATPs, one old dog
    Nerds be scary. :eek:
     
  13. May 17, 2012 at 8:47 PM
    #13
    wileyC

    wileyC Well-Known Member

    Joined:
    Oct 22, 2011
    Member:
    #65685
    Messages:
    2,924
    Gender:
    Male
    Centerville, OH
    Vehicle:
    2012 PreRunner Double Cab TRD Sport
    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.. :cool:
     
  14. May 17, 2012 at 8:50 PM
    #14
    wileyC

    wileyC Well-Known Member

    Joined:
    Oct 22, 2011
    Member:
    #65685
    Messages:
    2,924
    Gender:
    Male
    Centerville, OH
    Vehicle:
    2012 PreRunner Double Cab TRD Sport
    :typing::rockband:
    ;)
     
  15. May 31, 2012 at 4:28 PM
    #15
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    How would I parse to get a number for "May 31 2012 3:59:46 PM"?
     
  16. May 31, 2012 at 4:33 PM
    #16
    ImpulseRed008

    ImpulseRed008 Gone But Not Forgotten

    Joined:
    Sep 9, 2008
    Member:
    #9104
    Messages:
    39,235
    Gender:
    Female
    First Name:
    Susan
    SC
    Vehicle:
    08 PreRunner SR5 V6 w/tow pkg
    OEM SS tube steps, Access LE tonneau cover, pop n'lock, AVS in-channel vent visors, stubby antenna, Wet Okole seat covers, bed mat, rear diff breather mod, 4 extra d rings in bed, K&N air filter.
    :notsure:
     
  17. May 31, 2012 at 4:51 PM
    #17
    Country101

    Country101 Well-Known Member

    Joined:
    Apr 7, 2012
    Member:
    #76429
    Messages:
    443
    Gender:
    Male
    NW Arkansas
    Vehicle:
    08 tacoma access cab V6 4x4 manual
    dirty truck mod, custom trailer scratch on tailgate, husky floorliners, OME Lift
    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.....
     
  18. May 31, 2012 at 4:59 PM
    #18
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    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...
     
  19. May 31, 2012 at 4:59 PM
    #19
    Blasphemous

    Blasphemous [OP] Well-Known Member

    Joined:
    Apr 9, 2011
    Member:
    #54632
    Messages:
    643
    Gender:
    Male
    First Name:
    Mike
    San Fernando Valley, CA
    Vehicle:
    2012 TRD Sport DC 4x4
    TRD SC, Stealth Pulley, DTLT Headers, URD MK3 Catback Exhaust, AFE II Intake
    Btw I have just purchased "Excel for Dummies" on amazon. :)
     
  20. Jun 1, 2012 at 11:09 AM
    #20
    Country101

    Country101 Well-Known Member

    Joined:
    Apr 7, 2012
    Member:
    #76429
    Messages:
    443
    Gender:
    Male
    NW Arkansas
    Vehicle:
    08 tacoma access cab V6 4x4 manual
    dirty truck mod, custom trailer scratch on tailgate, husky floorliners, OME Lift
    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.
     
To Top