Forum Discussion
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 each row found
3. Find all of the next earliest date in Column A
4. Enter the next sequential number from the one entered previously in Column B for each new row found
5. Repeat until through the latest date in Column A
I am hoping I can make relatively quick work of this, at least compared to updating each of the 5,000 plus row manually!
Thanks in advance for any and all help!
=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.
I think this will be doable with a formula but first please confirm that you have Excel 365.
Next i don't understand what numbers you want in column B but I think they are almost like a rank number for the dates. earliest date is X, next earliest is X+1, etc... where all dates that are the same get that same value.
so something like:
=XMATCH(A:A,SORT(UNIQUE(A:A)))+X
but replace A:A with the actual range and ideally use table range formatting like Table1[Date]and replace X at the end with the offset you want to use (i.e. value -1 to be used for lowest date)
- Yes, I am using 365. The entered values would just be numbers in sequence. All of the first date would be "1", all of the next date would be "2", etc.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.
=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.
Thanks everyone! OliverScheurich had the response that worked best for my situation. I'm not all that familiar with Power Queries. The sheet Oliver provided worked like a charm! I'll pick through the other responses when I have the time to learn more about how they would work. Thanks again!
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.
