Forum Discussion
Solved
Find All Rows With Same Date And Input A Value In Another Cell
So this one is a bit complicated (I think) and might need to be done through VBA. I need to do the following: 1. Find all of a earliest date in Column A 2. Enter the same number in Column B for ...
=LET(rng,A1:A5000,
stacked,HSTACK(SORT(UNIQUE(rng)),SEQUENCE(COUNT(SORT(UNIQUE(rng))))),
BYROW(rng,LAMBDA(r,XLOOKUP(r,CHOOSECOLS(stacked,1),CHOOSECOLS(stacked,2)))))
Alternatively this formula returns the intended result in my Excel for the web sheet.
chemguy120 I'm sorry but apparently you didn't even try my formula. If you want to use something easy to understand it seems odd you went with the much more complicated formula. I literally just pasted my formula on the same sheet (ignored the +X since you are starting from 1 and voila:
for understanding it is take and sort the unique list of all dates and then go down the list of all dates and find where in that list each date is found.
That said, I highly recommend you dissect Oliver's formula for it will teach you about lots of other useful functions like LAMBDA and BYROW and HSTACK.
I agree. Your formula is shorter and easier to understand! Even in comparison to my Power Query suggestion i'd prefer your solution not just because it updates dynamically. Perhaps chemguy120 will give your formula a try and mark your solution as best response.