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.
ragomes1972 Hi. Should you prefer not to do it in power query, as suggested by SergeiBaklan, you may do it directly on the worksheet. Assuming excel 365 and setting upp two formulas;
G3 =UNIQUE(Table1[Sprint Commit])
to find the unique sprints.
G4 uses the unique sprints as 'local' name data and defines minData being the minimal date for each unique sprint.
Finally defines output as an xlookup of the combination of data (unique sprints) with their minimal date and returns the points.
You may change the last line from output to minDate or data to se how each of them turns out.
=LET(data;G3#;
minDate;MINIFS(Table1[Log Date-Time];Table1[Sprint Commit];data);
output;XLOOKUP(data & "|" & minDate; Table1[Sprint Commit] & "|" & Table1[Log Date-Time];Table1[Total points]);
output
)
The result is a little different than in the example.
Shuold you though need the first instead of the smallest timestamp, the formula could rather be
=XLOOKUP(G3#;Table1[Sprint Commit];Table1[Total points])
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.