Forum Discussion
How to find unique value and display the value based on the earliest date/time criteria
If you use Power Query the easiest way to generate second table is by it.
- reference first query
- sort by Sprint and Time, both ascending
- add Index to fix table in memory
- remove duplicates on sprints
- remove other columns
- load to sheet
Please check attached.
As a comment, I'm not sure what could be the reason for having Power Query table land it into the sheet and generate final result by formulae then. IMHO, much easier to add few extra steps to Power Query.
Anyway, if dynamic arrays I'd suggest to put result into one spill, not combine two separate spills. That could be like
=LET(
sorted, SORTBY( Table1, Table1[Sprint Commit],1, Table1[Log Date-Time],1),
sprints, UNIQUE( Table1[Sprint Commit] ),
INDEX( sorted, XMATCH( sprints, INDEX(sorted,0,1)), {1,3} ) )
> IMHO, much easier to add few extra steps to Power Query.
Sure is if the sheet user is a bit of a Power Query developer. But if a user gets a sheet loaded from someone else, data pasted from somewhere or for any other reason prefers to finalize on the spreadsheet, also your worksheet solution is a functional way to do it 🙂
Re one or two spilling cells, there are fmpov pros and cons with both solutions and in this case I suggested two cells.
eg, if the user wants a sum of the total points in table 2, it may be easier to understand a solution with two cells and the possibility to use SUM(H3#).
With one spilling cell it will be needed to use SUM(G3# H:H), SUM(INDEX(H3#,,2)) or any other way to reference the spilled data column.
By buiding it all in one though, one gets a solution with fewer formulas that can be manipulated by mistake so if it should be my own solution to in the future be managed solely by me or you, I might do it all in one cell.
In this case though, I consider educating in somehow smaller steps and reusing a limited set of functions that can be combined into a wide range of application areas.
Anyhow, ragomes1972 now have some different ways to solve the problem and I hope at least one of them will be a suitable one.