Forum Discussion
Assistance with a formula please
Hello - I would be very grateful for help with a formula please. Please see sample dataset (image below sorry couldn't see how to attach it!) I need the formula to look at each individual ID and th...
Kralin This is a pretty tall order for a dataset consisting of "many 1000s of rows". To help maintain an acceptable level of performance, the route I chose was to first generate a unique list of alerts by ID. I went through a few formula variations, the most efficient of which happened to be the longest:
=LET(
data, SORT(A2:C10001, {1,2}, {1,-1}),
id, CHOOSECOLS(data, 1),
next, id=DROP(VSTACK("", id), -1),
num, SCAN(0, next, LAMBDA(v,n, IF(n, v+1, 1))),
two, FILTER(data, num<3),
status, CHOOSECOLS(two, 3),
arr, FILTER(CHOOSECOLS(two, 1), (status="Did not attend")+(status="Cancelled")),
unq, UNIQUE(arr),
incl, BYROW(unq, LAMBDA(r, SUM(N(r=arr))=2)),
FILTER(unq, incl))
When tested with 10,000 rows of randomized data, there was a very slight hesitation (split second), but tolerable. With other methods I attempted, the lag was noticeably longer.
With the above-mentioned formula entered in cell F2, for example, the formula to generate "Y" or "N" in the "Alert" column could be:
=IF(A2=PreviousID, "", IF(COUNTIF($F$2#, A2), "Y", "N"))
...where the data is sorted by ID in ascending order, then by Date in descending order. Also, PreviousID is a defined name (in Name Manager) referring to =Sheet1!$A1 (relative row reference).
Please see the attached workbook, if desired...
djclementsthank you for taking the time and trouble to get back to me - much appreciated.
I have tried your formula and I get a 'function not valid' error.
I have a terrible feeling it may be because I'm using Excel Professional 2019, not a more recent version, and I should have mentioned that in the first instance. I'm terribly sorry.
Given this restriction, can the formula you (kindly) suggested be amended in any way to fit this earlier version of Excel?
Many thanks.
Kralin Yes, the formulas used in my first reply will only work with Excel for MS365. Please see the newly attached version, which should be compatible with Excel 2019. It uses similar logic, but with helper columns in a structured table in order to minimize the number of calculations necessary to get the desired result. It's not as robust as the MS365 version, but still performs alright with up to 5000 rows of data. If your dataset is larger than that, you may want to set Calculation Options to Manual while working with your data, then switch back to Automatic (or Calculate Now) to refresh the results.
Again, the formulas will only work properly if the table has been sorted by ID from Smallest to Largest, then by Date from Newest to Oldest:
Home > Sort & Filter > Custom Sort
I hope that works out for you. Cheers!
djclementsthanks once more for taking the time and trouble to reply. For some reason your solution wasn't accepted in 2019 - or the version we have at work, anyway. Most perplexing. Please don't worry about another solution, I'll just apply my thinking hat and try to get to the data in another way.
Thanks again.
