for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

1 lurker | 102 watchers

um Jul 2023 8:41am, 24 Jul 2023 7,563 posts	Quote Pin um I haven't used them, but do the Rank or Large function provide a solve? eg ablebits.com Or a quick and dirty solve ... use the pivot you already have to get the best/minimum time, then delete* those entries and you'll get their 2nd best. Delete again for their 3rd? *or modify the time by adding an hour or whatever
um Jul 2023 10:17am, 24 Jul 2023 7,564 posts	Quote Pin um Funnily enough, SMALL may be better than large. Not a pivot, but, depending on how many people you're looking for, SMALL lets you build an array of names and scores fairly easily ablebits.com (swapping subjects for names and noting it's an array formula, so you need <CTRL> <SHIFT> <ENTER> eg Where I've typed in (text) the bold cells, and the formula in E3 is {=SMALL(IF($A$2:$A$20=E$2,$B$2:$B$20), $D3)} (not the curly array formula brackets!) then dragged and dropped down and across the array Or if easier you could do it all in a new sheet with names as a column or row, whichever fits best
JK chameleon Jul 2023 2:57pm, 31 Jul 2023 17,799 posts	Quote Pin JK chameleon Sorry, I'm being a bit Excel needy today. I have a spreadsheet being generated through MS Forms with various data, and it includes the date/time stamp of when the form was submitted. So for the sake of an example, a line of data might have the time of the form submission, a unique reference number (URN), and whether that piece of work was a breach or an application. On a separate sheet, I'd like it to pull through certain data based on the date, so for example in the new sheet there will be a box for today's (for example) date to go in, and then I'd like the sheet to use that date to give a list of all the form responses from that date. Using the above example, I'd like it to produce a list of all the URNs from that date, and whether each one was a breach or application. I'm not sure an XLOOKUP will work here as that wants to match exact data, and there will also be blank entries as some days there will be around 40/50 entries, but other days only about 10/20. I don't want to use a pivot table, as (a) the data would need to be refreshed and my Admin team aren't very Excel-smart, and (b) I'd like the table to build in real time, so that Admin can see entries pop up as they complete the allocation form. Any thoughts or guidance would be appreciated - my usual work oracle is off unwell as he tore some ligaments in his knee trying to demonstrate to some people that his knee can no longer put up with what he used to do...
um Jul 2023 3:34pm, 31 Jul 2023 7,581 posts	Quote Pin um What version have you got? Supposedly, from 2019 onwards (or 365), there's the Filter function support.microsoft.com (mine's 2016, so I can't validate)
JK chameleon Jul 2023 3:57pm, 31 Jul 2023 17,801 posts	Quote Pin JK chameleon That won't work for my use - I need the data to be presented separately from the main data table, so that I can then display some stats about the day's allocations. The idea of the sheet is that Admin allocate a case to an officer (using a Form). This form pushes through to an auto-filled spreadsheet. From there I want the data from "today's" allocations to appear automatically on a separate worksheet, so that they can see at a glance how many cases each officer has had.
Bros Van Aard 🇬🇧 Jul 2023 4:52pm, 31 Jul 2023 103 posts	Quote Pin Bros Van Aard 🇬🇧 Are you sure Filter won't work? Try this on a new sheet: =FILTER(Sheet1!A:C,(Sheet1!A:A=TODAY())*(Sheet1!A:A<(TODAY()+1))) This will only work if you have a recent version of excel and you'll need to change the references to fit your sheet, and change TODAY() for your chosen date, but this would be the neatest solution I can think of. Otherwise, I would have said try Power Query but that has the same problems as using pivot tables.
um Jul 2023 5:00pm, 31 Jul 2023 7,582 posts	Quote Pin um I can't replicate it, but isn't that what the filter function does? Just put it in another sheet and reference the data in the first sheet, with an input date, or just defaulting to today's date? I have found a way to create a dynamic array, well, a set of cells, using a fairly complex index, match, indirect & concatenate formula if of interest. It would need a bit of error handling to print blanks rather than error messages once it's got to the end of the data.
JK chameleon Jul 2023 6:05pm, 31 Jul 2023 17,802 posts	Quote Pin JK chameleon Aaaagh, sorry. I was being a royal idiot and conflating the Filter function with the auto-filter thingy. Apologies. Trying to have a play with the FILTER function, not able to get it working atm but I think that's my brain being an idiot rather than anything else. I'll have another go with a clear head, might come back for some finesse!
run free Aug 2023 12:51am, 1 Aug 2023 5,316 posts	Quote Pin run free JK - if you're having difficulty check MS support support.microsoft.com Both Power Query and Pivot Tables can be refreshed automatically for a specific time period - see the Connection Properties. Should be okay for a small data set.
um Aug 2023 8:14am, 1 Aug 2023 7,585 posts	Quote Pin um I have my 'basic' excel 2016 model working now. It is a thing of beauty, but of the type that should never see the light of day. The best and worst of Excel. It does what it should. But in a few days time, it would be impossible to maintain. 1) data in sheet 1 2) results in sheet 2 based on typing a date into cell B1 The formulae? You'll love this ... with error handling to return 'no match' if none found in the first row then blanks after no more to be found. The tricky bit was working out the rolling match syntax to increment after each one found. Cell A3 : =IF(ISERROR(MATCH($B$1,Sheet1!$A$2:$A$9999,0)),"no match",MATCH($B$1,Sheet1!$A$2:$A$9999,0)+1) Cell A4 : =IF(ISERROR(MATCH($B$1,INDIRECT(CONCATENATE("Sheet1!A"&($A3+1)&":a9999")),0)+$A3),"",MATCH($B$1,INDIRECT(CONCATENATE("Sheet1!A"&($A3+1)&":a9999")),0)+$A3) (and then dragged down as far as you want to cover max results) Cell B3 : =IF($A3="no match","",INDIRECT("Sheet1!A"&$A3)) Then dragged sideways and final col switched ... Cell B4 : =IF(A4="","",INDIRECT("Sheet1!A"&$A4)) Dragged sideways with cols corrected, then down as far as you want ... (In my defence, it was raining and I was bored)