Forum Discussion
How to find unique value and display the value based on the earliest date/time criteria
Hi,
I have to find unique values from column A in a table generated via Power Query, and based on another column that displays date/time (in column B) find the earliest date and display the total from column C for each distinct unique value from A.
I tried different methods but wasn't able to succeed so I'd appreciate any help here.
Please find attached sample with explanation.
Thanks!
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.
5 Replies
Source : https://www.extendoffice.com/documents/excel/3333-excel-find-earliest-date-based-on-criteria.html
Kutools for Excel’s Advanced Combine Rows feature can help you to get the earliest or latest date of each item in Column A without any formulas.
https://www.extendoffice.com/product/kutools-for-excel.html : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. https://www.extendoffice.com/download/kutools-for-excel.html
Free Trial 60 dayshttps://www.extendoffice.com/order/kutools-for-excel.html
PayPal / MyCommerceIf you have installed Kutools for Excel, please do as follows:
1. If you want to keep the original data, you can copy and paste the data to another range, and then select the pasted range that you want to use.
2. Then click Kutools > Content > Advanced Combine Rows, see screenshot:
3. In the Combine Rows Based on Column dialog box, please click the column name that you want to get the date based on, and then click Primary Key option, see screenshot:
4. Then click the date column that you want to return the latest or earliest date, and select Calculate > Max / Min, ( select Max to get the latest date and Min to return the oldest date), see screenshot:
Website : https://www.gnitcm.net/today-jobs/
Tips: If you check My data has headers, your header within the data range will be ignored automatically, to get the correct date format, please uncheck Use formatted values option.
5. Then click Ok button, the latest or oldest date base on each item has been returned as follows:
Source : https://www.extendoffice.com/documents/excel/3333-excel-find-earliest-date-based-on-criteria.html
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.
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.
