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 Titans

    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. hipsterjoe

    hipsterjoe Well-Known Member
    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
     
    sem, jorge, Louis Holth and 6 others like this.
  7. hipsterjoe

    hipsterjoe Well-Known Member
    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. hipsterjoe

    hipsterjoe Well-Known Member
    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. hipsterjoe

    hipsterjoe Well-Known Member
    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 He ain’t me, he can’t be me
    Donor
    Auburn TigersAtlanta BravesAtlanta FalconsChelseaAtlanta United

    You can always just edit the template
     
  14. hipsterjoe

    hipsterjoe Well-Known Member
    Donor TMB OG
    Kansas City RoyalsDSA

    no I can’t
     
  15. comrade static

    comrade static i love french fries
    Donor
    Oklahoma City ThunderOklahoma Sooners

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

    hipsterjoe Well-Known Member
    Donor TMB OG
    Kansas City RoyalsDSA

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

    Louis Holth David LaChapelle levels of not giving a fuck
    Donor
    South Carolina GamecocksAtlanta BravesCharlotte HornetsCarolina 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
    Houston AstrosHouston RocketsLiverpoolTexas Tech Red Raiders altTravelHouston Oilers

    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 MountaineersPhoenix SunsPittsburgh PenguinsTiger WoodsNational LeagueBarAndGrillCoors 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.
     
    skiedfrillet and rickyrubio4life like this.
  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.

     
  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!

    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. hipsterjoe

    hipsterjoe Well-Known Member
    Donor TMB OG
    Kansas City RoyalsDSA

    can you help me too
     
  30. Henry Blake

    Henry Blake No Springsteen is leaving this house!

    Sure, if I can...
     
  31. hipsterjoe

    hipsterjoe Well-Known Member
    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. hipsterjoe

    hipsterjoe Well-Known Member
    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!

    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. hipsterjoe

    hipsterjoe Well-Known Member
    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.