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
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.
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
Built my first interactive dashboard today following this video. Really thorough tutorial and easy to follow.
If you're comfortable w VBA: 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
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
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
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
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.
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.
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.
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
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.
Do you subtract 27 hours for every case? Could you post a screenshot of what your spreadsheet looks like?
I may be missing some of the complexity here but I feel like this has everything you need? https://support.microsoft.com/en-us/office/add-or-subtract-time-16aa6697-6d6e-49c1-8e2c-3398a7cad6ad (note the bit about the special cell format for the total)
Like this? I did it in sheets just using straight substraction of the two fields. Make sure you have the date/time formatting correct.
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.
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?