| --- Log opened Tue Oct 19 00:00:58 2021 |
| 01:12 | | Vornicus [Vorn@ServerAdministrator.Nightstar.Net] has quit [Connection closed] |
| 03:19 | | m4221 [m4221@Nightstar-uft.bfq.70.38.IP] has joined #code |
| 03:19 | < m4221> | greetings, I am human |
| 03:20 | < m4221> | i seek intelligent conversation :D |
| 03:33 | <&Reiver> | All jokes aside, code may not in fact be the place to find it |
| 03:34 | <&Reiver> | This is a pretty specialist channel where the networks programmer nerds discuss/debate/assist/query one another on various mad projects or programming issues or paradigms or straight up bugfixing |
| 03:34 | <&Reiver> | But unless there's a specific issue on the list, it tends to stay pretty quiet, by design. |
| 03:35 | <&Reiver> | (Means that when it lights up people know to look rather than it being the usual 'hi' 'hey' 'how're you' 'good' stuff.) |
| 03:35 | <&Reiver> | However, with all that in mind, if you enjoy programmer and programmer-adjacent topics... you're welcome to stick about until something /does/ come up. |
| 03:35 | < m4221> | Reiver, mad projects? |
| 03:35 | < m4221> | like doc brown projects? |
| 03:35 | < m4221> | with time travel? |
| 03:36 | <&Reiver> | It's just a side channel to other more social ones elsewhere, as opposed to being a conversation channel itself. |
| 03:36 | < m4221> | arg--no shitting on java here then |
| 03:36 | <&Reiver> | Oh, you know. One of us will try to create an object-oriented capability in clojure, or someone will dive into truly terrifying bits of C code, or some poor sap will complain about SQL yet again and I'll break out the analytic functions or antijoins or whatever is the flavor of the week |
| 03:37 | < m4221> | I--fucking--love--C |
| 03:37 | <&Reiver> | I mean, if you're dealing wiht java and it's driving you nuts, go for it. Someone might even be able to help! |
| 03:37 | | Degi_ [Degi@Nightstar-k36qmo.pool.telefonica.de] has joined #code |
| 03:37 | <&Reiver> | But I wouldn't go 'topic of the day for shits and giggles' on it. |
| 03:37 | < m4221> | I don't get some company's fetishes with java |
| 03:37 | <&Reiver> | Besides. I hate SQL. It is my primary working language. These facts may be related, but it doesn't mean there's any need to sneer at it. :p |
| 03:38 | < m4221> | x86 assembly too is my favorite pain :D |
| 03:38 | | Degi [Degi@Nightstar-vgk6q1.pool.telefonica.de] has quit [Ping timeout: 121 seconds] |
| 03:38 | | Degi_ is now known as Degi |
| 03:39 | < m4221> | Reiver, I am just on about why some stuff needs to be a friggin java webapp in webspher |
| 03:39 | < m4221> | e |
| 03:44 | <&Reiver> | Good question. Is there better options handy in their tech stack? |
| 03:44 | <&Reiver> | (And that would have been available when they made it?) |
| 03:45 | < m4221> | not at this time unfortunately |
| 03:47 | < m4221> | given that, I am a fan of fresh computer builds for each new version applied. |
| 03:47 | <&Reiver> | Well, then your answer is before you! |
| 03:47 | <&Reiver> | Some days you use the tech you've got, not the tech that's ideal. |
| 03:48 | < m4221> | indeed we do |
| 03:48 | <&Reiver> | Especially if you're an enterprise setup rather than a startup, for instance, the cost to each new technology stack is a very real consideration. |
| 03:49 | <&Reiver> | Because some bastard's gonna have to maintain that thing later, and when it's ten years old and no longer shiny and cool it doesn't matter whether it was the 'best' solution as much as it does whether it's one of the stacks they have expertise in maintaining. |
| 03:49 | < m4221> | ^that goes back to my build fresh for each version upgrade |
| 03:50 | < m4221> | ^then again, that is probably my OCD tendencies there |
| 05:08 | | Vorntastic [uid293981@Nightstar-phvupn.irccloud.com] has joined #code |
| 05:08 | | mode/#code [+qo Vorntastic Vorntastic] by ChanServ |
| 05:17 | | himi [sjjf@Nightstar-v37cpe.internode.on.net] has quit [Ping timeout: 121 seconds] |
| 05:18 | | * Reiver pokes Vorntastic ponderingly |
| 05:18 | <&Reiver> | Is there a sane way to try to turn a string of text into a number so I can run a conditional formatting over it? |
| 05:18 | <&Reiver> | Like, I don't even especially care what the number /is/~ |
| 05:19 | <&Reiver> | But I'd like 'em to be multicolored. |
| 05:19 | <~Vorntastic> | I'm confused |
| 05:19 | <&Reiver> | Excel has conditional formatting, including the ability to apply color scales |
| 05:19 | <&Reiver> | I have a bunch of text I would like to be able to highlight unique values for |
| 05:20 | <~Vorntastic> | There's a unique values conditional formatting does that not work |
| 05:20 | <&Reiver> | So that everything labeled AMS is eg green ad everything labeled BOOKS is labeled blue or whatever |
| 05:21 | <&Reiver> | Sorry, I mean 'each string of text has a unique color for it', not 'each unique text etc' |
| 05:21 | <~Vorntastic> | Ah |
| 05:21 | <~Vorntastic> | Well, color scales would "work" but probably give you too-similar colors |
| 05:22 | <~Vorntastic> | I'm not at my desk, i can't fight with conditional formatting from here |
| 05:22 | <&Reiver> | color scales don't work at all |
| 05:22 | <&Reiver> | No problem, sorry ol chap |
| 05:23 | <&Reiver> | But yeah, this is text, so color scales don't operate |
| 05:23 | <&Reiver> | They want numbers to iterate on |
| 05:23 | <&Reiver> | I was wondering if I had a clever formula I could shove into the Conditional Formatting rules to spit out a number to work on~ |
| 05:24 | < Mahal> | like "if b3="books" paint column C blue"? |
| 05:24 | < Mahal> | that's entirely doable, I've done it before |
| 05:24 | < Mahal> | you don't need to enumerate a word value to make it happen |
| 05:24 | <&Reiver> | Without specifying any particular text, yes |
| 05:25 | <&Reiver> | Because there is going to be a /lot/ of different text and I would rather this was set up that 'new string of text' -> new color |
| 05:25 | <&Reiver> | (It's a calendar) |
| 05:25 | < Mahal> | OIC |
| 05:25 | <&Reiver> | (Yes, in excel) |
| 05:25 | <&Reiver> | (I pointed this out and was overruled so here I am eh) |
| 05:25 | < Mahal> | https://www.myexcelonline.com/blog/how-to-highlight-unique-values-in-excel/ |
| 05:25 | < Mahal> | ? |
| 05:26 | < Mahal> | oh wait, you need _different_ |
| 05:26 | < Mahal> | hmmmm |
| 05:26 | <&Reiver> | Non-unique |
| 05:26 | <&Reiver> | I can have half a dozen blocks with AMS |
| 05:26 | <&Reiver> | And then three rows with BOOKS |
| 05:26 | <~Vorntastic> | Rank |
| 05:26 | <&Reiver> | And then two rows with BOOS, CATS |
| 05:27 | <&Reiver> | Rank /could/ work |
| 05:27 | <&Reiver> | I would prefer... a hash, basically |
| 05:27 | <&Reiver> | So that way if I put a new text in it does not recolor everything but AMS was /always/ green even when I then put in AARDVARKs |
| 05:27 | <~Vorntastic> | Ay. |
| 05:30 | <&Reiver> | Not the best, I know |
| 05:30 | <&Reiver> | I would accept a rank I guess |
| 05:30 | <&Reiver> | This whole setup is stupid anyway, idk |
| 05:30 | <&Reiver> | I would even accept alternating, hell |
| 05:31 | <&Reiver> | maybe I'll just do a dumb-as-bricks helper column and format over that way, idk |
| 05:31 | < Mahal> | helper colun, locked-down dropdowns to book out time, conditional match on contents...? |
| 05:31 | | himi [sjjf@Nightstar-v37cpe.internode.on.net] has joined #code |
| 05:31 | | mode/#code [+o himi] by ChanServ |
| 05:32 | <&Reiver> | Pretty much I guess |
| 05:32 | <&Reiver> | Time is blocked out by start date and end date and then the cells fill in |
| 05:32 | <&Reiver> | our dear Vorn managed to Space Wizard that one |
| 05:32 | <&Reiver> | I never did fix it to work properly in weeks, couldn't figure it out enough to do it |
| 05:32 | <&Reiver> | But at this point this sheet is no longer a fun puzzle but an annoyance with a deadline so fuckit |
| 05:34 | <&Reiver> | tbh if there were a numerical hash in the system that would work fine |
| 05:34 | <&Reiver> | but AFAICT there is not so |
| 07:09 | | m4221 [m4221@Nightstar-uft.bfq.70.38.IP] has quit [[NS] Quit: Leaving] |
| 08:19 | | Kindamoody[zZz] is now known as Kindamoody|afk |
| 09:25 | | catalyst_ [catalyst@Nightstar-ejd4sd.cable.virginm.net] has joined #code |
| 09:25 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has quit [Connection closed] |
| 09:50 | | abudhabi_ [abudhabi@Nightstar-pkv1l4.adsl.tpnet.pl] has joined #code |
| 09:54 | | abudhabi__ [abudhabi@Nightstar-joqltj.adsl.tpnet.pl] has quit [Ping timeout: 121 seconds] |
| 11:51 | <&Reiver> | Okay, new puzzle |
| 11:51 | <&Reiver> | Well, an old puzzle but the puzzle ended up not being solved |
| 11:53 | <&Reiver> | I have strings of text in a big ol' set of columns, first one specifies which set of data (PROD, TEST, PROJ etc), second column contains text which ranges from pure alpha descriptions ... but also includes a very specific set of data of '2021B Upgrade' in which the first five characters are vitally importaint to be able to sort and find the maximal value. |
| 11:53 | <&Reiver> | Of course, these fuckin things are strings aren't they, and worse yet, text strings |
| 11:53 | <&Reiver> | How do I sort this shite |
| 11:53 | <&Reiver> | LEFT does not seem to enjoy being handed a data range. |
| 11:54 | <&Reiver> | Do I make another calc column? Or is there a clever trick I've missed? |
| 11:54 | <&Reiver> | 'cuz I'm headed the calc column route before long on this one >_> |
| 12:23 | <~Vorntastic> | You have to have these problems when I'm not at my PC don't you |
| 12:28 | <&Reiver> | hahaha sorry my dude |
| 12:28 | <&Reiver> | Well I have a partial solution, sort of |
| 12:28 | <&Reiver> | I now have a VERSION column sitting there that converts the text to either 0 if invalid, or eg 2021.2 (or 2021.1 if it was sitting on 2021A, 2021.3 if it was on 2021C, etc) |
| 12:29 | <&Reiver> | That then lets me run a simple enough MAX and capture magnitude |
| 12:30 | <&Reiver> | Now I just need to wrangle the XLOOKUP to run over that column, plus its Environment column, so I can say "The biggest number we have for Environment X, no later than date Y, is 2021.2, therefore we return =LEFT(texthere,5) as our result" and everyone is happy |
| 12:30 | <&Reiver> | Nothin' to it eh |
| 12:32 | <&Reiver> | (Environment: Column A, Project Title: Column B, Startdate G, EndDate H, Version I, yesiree this isn't a monstrosity at all) |
| 12:32 | <&Reiver> | ((I figured I /could/ hide some calculated columns in the middle of data entry, but maybe best I didn't and just left 'em at the end >_>) |
| 12:33 | <&Reiver> | I tell ya though, XLOOKUP and SUBTOTAL make me realise I am not the Excel master I thought I was >_> |
| 12:34 | <&Reiver> | I sort of understand them individually |
| 12:35 | <&Reiver> | But when I start messing with 'Give me the maximum value of column B for a given set based on column A, further restricted by dates in column H...' |
| 12:38 | <&Reiver> | It's something in the booleans I know it |
| 12:38 | <&Reiver> | and yet |
| 12:46 | <&Reiver> | oh, Filter does... half of it, hm |
| 12:46 | <&Reiver> | Now to do the other half |
| 12:46 | <&Reiver> | Booleans are going to do my head in I am sure of it but we can get there I am also sure |
| 12:53 | <&Reiver> | Okay Booleans can do it |
| 12:54 | <&Reiver> | But how do I Max inside my filter, to only return the row with the single biggest value per [other filter categories here]? |
| 12:54 | <&Reiver> | (This one might even be within the realm of Vorntastic answering from the top of his head) |
| 12:55 | < jeroud> | "A hat." |
| 12:55 | <~Vorntastic> | First guess, xlookup with highest below behavior and a Very Large search term |
| 13:03 | <&Reiver> | ho boy |
| 13:03 | <&Reiver> | hm |
| 13:03 | <&Reiver> | Filter does almost everything |
| 13:03 | <&Reiver> | But I cannot MAX it at the last minute unless I stick to the numeric versioning |
| 13:03 | <&Reiver> | I guess I could be really awful and just translate it back out again... |
| 13:25 | <&Reiver> | ... wait, that fucking worked? |
| 13:25 | <&Reiver> | That... fucking worked |
| 13:25 | <&Reiver> | ahahaha |
| 13:27 | < jeroud> | \o/ |
| 13:27 | <&Reiver> | GAZE UPON YE HORRORS AND DESPAIR |
| 13:27 | <&Reiver> | =CONCAT(LEFT(MAX(FILTER(Dates!$I:$I,(Dates!$A:$A=C2) * (Dates!$D:$D<=TODAY()),"Uncertain")),4),CHAR(MID(MAX(FILTER(Dates!$I:$I,(Dates!$A:$A=C2) * (Dates!$D:$D<=TODAY()),"Uncertain")),6,2))) |
| 13:27 | <&Reiver> | But it works |
| 13:27 | <&Reiver> | So I don't caaaare |
| 13:27 | < jeroud> | You will when you have to maintain it. :-P |
| 13:28 | <&Reiver> | Yes, sir, the solution ended up be 'filter for the numeric value (which includes an ASCII value for A vs B vs C as a decimal place) that then gets MAX'd, and then split out, and de-ASCII'd back to an actual character and concatenated together at the end as a glorious abomination of transmitting text information as a numeric' |
| 13:28 | <&Reiver> | Good thing I only ever have to worry about one letter |
| 13:28 | <&Reiver> | Unless they change the entire naming scheme on me of course |
| 13:28 | <&Reiver> | They'd never do that |
| 13:28 | <&Reiver> | ever |
| 13:28 | <&Reiver> | right |
| 13:28 | | * Reiver fleeeeee |
| 13:29 | <&Reiver> | it's fine. It's fine. |
| 13:29 | <&Reiver> | I somehow broke my 'alternating groups' code in the process, but it's fine I'm sure I can fix that. |
| 13:29 | <&Reiver> | In the meantime this sucker is almost starting to look practical. Can't believe it. |
| 13:31 | < jeroud> | How practical would it be to write Actual Code for this? |
| 13:33 | <&Reiver> | You mean like not excel |
| 13:33 | <&Reiver> | Or you mean 'learn how to combine xlookup and maximum value searches' |
| 13:33 | < jeroud> | Well, VBA or whatever. |
| 13:33 | <&Reiver> | The latter probably not hard if it's Vorn |
| 13:33 | <&Reiver> | oh, that |
| 13:33 | <&Reiver> | impossible |
| 13:34 | <&Reiver> | This is to be a flat Excel spreadsheet |
| 13:34 | < jeroud> | Ah. |
| 13:34 | <&Reiver> | No macros to break or depreciate |
| 13:34 | <&Reiver> | No linked live features to break links |
| 13:34 | <&Reiver> | No fucking Outlook Calendar to do this entire thing in one go |
| 13:34 | | * Reiver ahems |
| 13:34 | <&Reiver> | But of course enough about me. |
| 13:35 | | * TheWatcher patpats |
| 13:35 | <&Reiver> | How're you? Lovely day, isn't it? |
| 13:35 | | * jeroud offers a hug. |
| 13:35 | | * Reiver returns it. |
| 13:35 | <&Reiver> | But yes. |
| 13:35 | <&Reiver> | Tomorrow: Fix my busted color-alternating stuff that broke somewhere this evening idk |
| 13:35 | <&Reiver> | Once I've done that, call it done |
| 13:35 | <&Reiver> | yey |
| 13:35 | | * Reiver goes to bed. |
| 13:36 | | * Reiver could have left it for tomorrow but was actually enjoying the peace. |
| 13:36 | <&Reiver> | (And my D&D discussions fell through and I didn't feel like SpaceRPG so I may as well do *something* useful, lol) |
| 13:36 | <&Reiver> | g'night~ |
| 13:36 | < jeroud> | G'night Reiver. Sleep well. |
| 13:38 | <&Reiver> | nini |
| 14:11 | | Kindamoody|afk is now known as Kindamoody |
| 14:58 | | Vornicus [Vorn@ServerAdministrator.Nightstar.Net] has joined #code |
| 14:58 | | mode/#code [+qo Vornicus Vornicus] by ChanServ |
| 16:27 | <@sshine> | hm, I'm beginning to feel that I won't get a Hacktoberfest t-shirt this year. too many constraints. |
| 16:27 | <@sshine> | I've found a repository that I started contributing to. the author even wrote back and was pretty happy. |
| 16:28 | <@sshine> | but he's not responsive enough wrt. fulfilling the requirements (adding proper tagging of the repository, etc.) so it seems like it won't qualify. I discovered a bit late (Oct 15th), and he's just busy. I'm not gonna blame him, because I think his intention is that he's happy I'll boost his existing effort, but that he uses a certain amount of time on it. |
| 16:30 | <@sshine> | it feels like I'd need to "fake" it to make the required number of contributions in time. i.e., agree with someone to set it up, do some rapid contributions, and be done. on top, only 55k people get the t-shirts, so it feels like a gamble. I think I'll just buy a cool t-shirt instead. things get pretty shitty when they get hyped, imho. |
| 16:31 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has joined #code |
| 16:31 | | catalyst_ [catalyst@Nightstar-ejd4sd.cable.virginm.net] has quit [Connection closed] |
| 17:17 | | Vorntastic [uid293981@Nightstar-phvupn.irccloud.com] has quit [[NS] Quit: Connection closed for inactivity] |
| 17:23 | | Emmy [Emmy@Nightstar-l49opt.fixed.kpn.net] has joined #code |
| 19:00 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has quit [[NS] Quit: -a- Connection Timed Out] |
| 19:01 | | Emmy [Emmy@Nightstar-l49opt.fixed.kpn.net] has quit [NickServ (RECOVER command used by M-E)] |
| 19:01 | | Emmy [Emmy@Nightstar-l49opt.fixed.kpn.net] has joined #code |
| 19:01 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has joined #code |
| 19:47 | | catalyst_ [catalyst@Nightstar-lq7qg8.dab.02.net] has joined #code |
| 19:49 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has quit [Ping timeout: 121 seconds] |
| 20:49 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has joined #code |
| 20:51 | | catalyst_ [catalyst@Nightstar-lq7qg8.dab.02.net] has quit [Ping timeout: 121 seconds] |
| 22:26 | | Emmy [Emmy@Nightstar-l49opt.fixed.kpn.net] has quit [Ping timeout: 121 seconds] |
| 23:11 | | Kindamoody is now known as Kindamoody[zZz] |
| 23:13 | | catalyst [catalyst@Nightstar-ejd4sd.cable.virginm.net] has quit [Connection reset by peer] |
| 23:15 | | catalyst_ [catalyst@Nightstar-ejd4sd.cable.virginm.net] has joined #code |
| --- Log closed Wed Oct 20 00:00:59 2021 |