Excel dudes

Discussion in 'The Mainboard' started by skiedfrillet, Jul 31, 2018.

  1. Jax Teller

    Jax Teller Well-Known Member
    Donor
    Alabama Crimson TideAtlanta BravesTennessee TitansNashville PredatorsNashville SC

    You're late buddy. Where Eagles Dare already set me straight on discipline. :nahnah:
     
  2. TDCD

    TDCD Handling the Fisher account
    Donor
    Florida State SeminolesConnecticut HuskiesBoston Red SoxNew England Patriots

    download node.js and run this

    Code:
    const fs = require('fs');
    const path = require('path');
    const file = fs.readFileSync(path.resolve(__dirname, '</path/to/file/>')).toString();
    
    const lines = file.split('\n');
    const ids = {};
    lines.forEach(line => {
      const [id, email] = line.split(',');
      if (!ids[id]) {
        ids[id] = []
      }
      ids[id].push(email)
    });
    
    const newFile = '';
    Object.keys(ids).forEach(id => {
      newFile += id += ',' += ids[id].join(',') += '\n';
    });
    
    fs.writeFileSync(path.resolve(__dirname, '<path/to/new/file'), newFile);
    pls venmo $200 to @ tdcd thx
     
    bigred77 and racer like this.
  3. colonelrascals

    colonelrascals Mayonnaise-colored Benz, I push miracle whips
    Donor
    Texas Tech Red RaidersHouston AstrosHouston RocketsTennisTottenham HotspurTexas Tech Red Raiders alt

    this would read so much better in python
     
    Where Eagles Dare likes this.
  4. TDCD

    TDCD Handling the Fisher account
    Donor
    Florida State SeminolesConnecticut HuskiesBoston Red SoxNew England Patriots

    [​IMG]

    memed to death. rip
     
    and Henry Blake like this.
  5. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    who is the biggest excel nerd here? I need help building a UTM tracker spreadsheet
     
  6. skiedfrillet

    skiedfrillet It's not a lie if you believe it.
    Donor
    Clemson Tigers

    it's me

    i am that man
     
    Tobias, aisle seven, jorge and 7 others like this.
  7. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    I'm finna send you a PM
     
  8. angus

    angus Well-Known Member
    Donor

    Read the OP before you make any decisions.
     
    mangler, , sem and 6 others like this.
  9. rickyrubio4life

    rickyrubio4life Straight Cash Homey

  10. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    sem, Louis Holth, dirtybert and 3 others like this.
  11. rickyrubio4life

    rickyrubio4life Straight Cash Homey

    I have located the Oracle for you.

    UTM sheet.png
     
  12. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    I want something a bit different than what templates can offer me
     
    rickyrubio4life likes this.
  13. Arrec Bardwin

    Arrec Bardwin La Araña Discoteca
    Donor
    Auburn TigersAtlanta BravesAtlanta FalconsChelseaAtlanta United

    You can always just edit the template
     
  14. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    no I can’t
     
  15. comrade static

    comrade static i love french fries
    Donor
    Oklahoma City ThunderPhiladelphia EaglesOklahoma SoonersBarAndGrillBig 8 Conference

    you can do anything you put your mind to
     
    shawnoc likes this.
  16. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    except get a spreadsheet to generate multiple unique UTM codes for one link
     
  17. Louis Holth

    Louis Holth but we also just might be those motherfuckers
    Donor
    South Carolina GamecocksAtlanta BravesCarolina PanthersTottenham Hotspur

    Damn this the thread that keeps on giving
     
  18. jorge

    jorge Founder of Post ITT if your team sucks
    Donor
    Penn State Nittany Lions

    I like VLOOKUP better than INDEX/MATCH
     
  19. DriveByBBQ

    DriveByBBQ Well-Known Member
    Donor
    Alabama Crimson TideTiger WoodsBirmingham LegionPGAGrateful DeadUnited States Men's National Soccer Team

    Formula assistance requested: I need to sum the first 6 non-zero values in a range.Range is located in a column on a seperate sheet within the workbook.
     
  20. steamengine

    steamengine I don’t want to press one for English!
    Donor
    Duke Blue DevilsHouston AstrosKansas City ChiefsLiverpool

    Fight me
     
  21. pianoman

    pianoman my drinks are free
    Donor TMB OG
    Michigan WolverinesChicago CubsDetroit Red Wings

    Do a SUMIF and put this in for the criteria, "<>0" . With the quotation marks included.
     
  22. devine

    devine hi, i am user devine
    Donor
    West Virginia MountaineersChicago BullsPhoenix SunsPittsburgh PenguinsManchester CityPittsburgh PiratesSan Diego PadresBarAndGrillCoors Light

    People tell me I excel at a lot of things. How can I be of assistance
     
    rickyrubio4life likes this.
  23. angus

    angus Well-Known Member
    Donor

    This thread isn't for actual assistance, it's for making fun of skillet.
     
  24. DriveByBBQ

    DriveByBBQ Well-Known Member
    Donor
    Alabama Crimson TideTiger WoodsBirmingham LegionPGAGrateful DeadUnited States Men's National Soccer Team

    I was trying to sum the first six non-zero values in a dynamics range. The "6" is dynamic in a data validation drop down. Thanks for the response. Here's the formula I ended up writing,

    =SUMIFS($G170:$FA170,$G$8:$FA$8,"<="&TEXT($F175+$C$17-1,"#"),$G$8:$FA$8,">="&TEXT($C$17,"#"))*-1
     
  25. DriveByBBQ

    DriveByBBQ Well-Known Member
    Donor
    Alabama Crimson TideTiger WoodsBirmingham LegionPGAGrateful DeadUnited States Men's National Soccer Team

    Built my first interactive dashboard today following this video. Really thorough tutorial and easy to follow.

     
    HuskerInMiami likes this.
  26. pianoman

    pianoman my drinks are free
    Donor TMB OG
    Michigan WolverinesChicago CubsDetroit Red Wings

    Ah, I missed that. In that situation I’d write a macro with a do while loop that would sum them.
     
    Henry Blake likes this.
  27. Where Eagles Dare

    Where Eagles Dare The Specialist Show On Earth
    Donor
    Auburn TigersAtlanta BravesWashington Football TeamAtlanta United

    In excel?

    Just use Power Bi
     
    THF likes this.
  28. Henry Blake

    Henry Blake No Springsteen is leaving this house!
    Donor

    If you're comfortable w VBA:

    upload_2020-5-22_19-14-43.png

    Place in a macro (you could customize the max number of values to add if you wanted):

    Function add(Arg1 As Range)
    the_sum = 0
    counter = 0
    For Each elem In Arg1
    If elem.Value > 0 And counter < 7 Then
    the_sum = the_sum + elem.Value
    counter = counter + 1
    End If
    Next
    add = the_sum
    End Function
     
    DriveByBBQ likes this.
  29. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    can you help me too
     
  30. Henry Blake

    Henry Blake No Springsteen is leaving this house!
    Donor

    Sure, if I can...
     
  31. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    do you know what UTM codes are? I can write up a detailed thing for you later if it’ll help
     
  32. BellottiBold

    Donor
    Oregon Ducks

    For URL tracking?
     
  33. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    yeah, but each UTM needs a source name (Facebook, Twitter, email, etc.) and I can’t figure out how to make a formula to automatically create multiple versions of the same URL with different UTM source codes
     
  34. Henry Blake

    Henry Blake No Springsteen is leaving this house!
    Donor

    Are you looking to create random UTMs with Facebook/etc. as the domain? e.g.:
    facebook.com/mypage/?utm=123
    facebook.com/mypage?utm=abc
    or
    facebook.com/mypage?utm=facebook_456
    facebook.com/mypage?utm=twitter_789
     
    #84 Henry Blake, May 23, 2020
    Last edited: May 23, 2020
    hipsterjoe likes this.
  35. pperc

    pperc Well-Known Member
    Donor

    You like using additional computing power and lack of flexibility?
     
    Henry Blake likes this.
  36. joe-

    joe- yesterday is a hard word for me
    Donor TMB OG
    Kansas City RoyalsDSA

    your last update is closer to accurate but not quite. it’ll take a while to type out but as soon as I can get on my computer I’ll type out a more detailed post
     
    Henry Blake likes this.
  37. jorge

    jorge Founder of Post ITT if your team sucks
    Donor
    Penn State Nittany Lions

    If my sheet doesn't take 5 minutes to compute, when will I get my coffee?
     
    pperc likes this.
  38. pperc

    pperc Well-Known Member
    Donor

    Are you a closer?
     
  39. kennypowers

    kennypowers Big shit like a dinosaur did it
    Donor TMB OG
    UCF KnightsAtlanta BravesJacksonville Jaguars

    Microsoft needs to stop being so stubborn with VBA (maybe the worst language ever) and make python the default for macros. Everything you guys are asking is so easy to do with python.
     
  40. Where Eagles Dare

    Where Eagles Dare The Specialist Show On Earth
    Donor
    Auburn TigersAtlanta BravesWashington Football TeamAtlanta United

    Or just write it in Python and populate a cvs/excel file
     
  41. kennypowers

    kennypowers Big shit like a dinosaur did it
    Donor TMB OG
    UCF KnightsAtlanta BravesJacksonville Jaguars

    Yeah I mean that works for me, but I’m able to do all my processing the way I want. A lot of folks I’m assuming need to stay in excel for their co-workers sake.

    There’s also a benefit of using the already built in tools and GUI and just adding macros.
     
  42. Jax Teller

    Jax Teller Well-Known Member
    Donor
    Alabama Crimson TideAtlanta BravesTennessee TitansNashville PredatorsNashville SC

    Trying to figure out a formula for subtracting times. We have to use military times.
    Example. I have a truck that picks up at 17:15. To know when the returns truck should leave for the supplier we subtract the total route transit from the first stop and then add a 30 minute buffer.
    So this route has 27 hours of transit. Doing this manually would mean the returns truck needs to leave at roughly 14:15 the prior day (17:15 - 27:00 plus 00:30)
    I'm trying to make a formula to do this so we don't have to manually do it for 1800 routes a day.
     
  43. racer

    racer Yuma, where I work in software.
    Donor
    Iowa HawkeyesKansas City ChiefsLas Vegas Golden KnightsWatfordOlympicsFormula 1

    if A is your start time and B And C is 00:30is your route time
    =sum(A2-(b2-24))-c2 ?
    I had to put 00:30 in a cell because I couldn’t get 30 to sum as part of the formula.

    Do you have same-day routes? The math still works but this does not specify the day on which you need to leave
     
    #94 racer, Sep 3, 2020
    Last edited: Sep 3, 2020
    Jax Teller likes this.
  44. Jax Teller

    Jax Teller Well-Known Member
    Donor
    Alabama Crimson TideAtlanta BravesTennessee TitansNashville PredatorsNashville SC

    We have same day, next day, 2 day, etc lol. But the start time of the returns load is what I'm trying to get to. So pickup at 17:15 minus 27 hours of transit then that plus 30 minute buffer = start time of returns truck. Just doing a regular subtraction either errors cause of it being negative or I can make it show but it comes up as just the difference (like -9:45) which doesn't help.
    Hell there might not be a way.
    When route transit is less than the pickup time it's a lot easier but those are the minority of our routes.
     
  45. Henry Blake

    Henry Blake No Springsteen is leaving this house!
    Donor

    Do you subtract 27 hours for every case? Could you post a screenshot of what your spreadsheet looks like?
     
  46. BellottiBold

    Donor
    Oregon Ducks

  47. $P1

    $P1 Ball State #1
    Staff Donor TMB OG
    Michigan WolverinesChicago CubsLos Angeles LakersChicago BearsChicago BlackhawksWest Ham UnitedBall State Cardinals

    Like this? I did it in sheets just using straight substraction of the two fields. Make sure you have the date/time formatting correct.

    upload_2020-9-3_11-24-58.png
     
  48. HuskerInMiami

    HuskerInMiami Well-Known Member
    Donor
    Miami DolphinsNebraska Cornhuskers

    I have a program that only gives me amount of time in the format 2h45m when I download the Excel and I can't sort by highest amount of time because 56m shows higher than 2h45m . Anyone know how to change that? I've set up macro's and can't figure out how to fix this problem.
     
  49. $P1

    $P1 Ball State #1
    Staff Donor TMB OG
    Michigan WolverinesChicago CubsLos Angeles LakersChicago BearsChicago BlackhawksWest Ham UnitedBall State Cardinals

    Yeah that's being read as a text cell. Does 56m show as 0h56m by any chance? could delimit and split the columns after the "h" then sort by the two columns.

    If not and if you can't change the output format, you may be SOL


    Nothing I can do in Sheets with it, from my first look...but I don't have Excel anymore

    Might be something with the substitute or evaluates functions?