Forum Discussion
Find latest iteration of reference number
Can you rely upon the Iteration numbers being in increasing order down the sheet? If so, you can search for the final occurrence of each DRN and return the corresponding Category. I normally use a dynamic array to produce the results for the each record but, since you are using a table, the formula could be placed in the first record and it will propagate down the table.
= LET( category, XLOOKUP([@[DRN Number]], [DRN Number], [DRN Category],,,-1), IF(category=1, "closed", "accepted") )See the right-most field. If you have to check the order of the Iteration numbers, the formula would get more complicated.
BTW, it is best practice when using Tables not to allow empty records within the list or at the end of the table. The Table should grow automatically as data is added.
To achieve this in Excel, you can use a combination of functions and helper columns. It's often easier to break down complex tasks into smaller steps. Here's a step-by-step guide on how to accomplish your task:
- Helper Column (Latest Iteration): Create a helper column to identify the latest iteration for each reference number. You can use the MAXIFS function to find the latest iteration based on the reference number. Assuming your reference numbers are in column H and iterations are in column I, enter the following formula in your helper column (e.g., J2):
=MAXIFS($I$2:$I$100, $H$2:$H$100, $H2)
This formula finds the maximum iteration value for the current reference number in cell H2.
- Accepted or Closed: In another column, you can use the IF function to determine if the document is "Accepted" or "Closed." Assuming you want to display the result in column K, you can use a formula like this (in K2):
=IF(AND(AK2=1, J2>0), "Accepted", "Closed")
This formula checks if the value in column AK is 1 and if the Latest Iteration (from the helper column) is greater than 0. If both conditions are met, it returns "Accepted"; otherwise, it returns "Closed."
- Drag Down: Drag the formula in column K down to apply it to all rows.
Your spreadsheet should now have a column (K) that indicates whether each document is "Accepted" or "Closed" based on the criteria you provided.
The use of helper columns makes the logic easier to manage and understand. If you prefer a single formula, you can use nested IF functions, but it can become complex and harder to troubleshoot. The helper column approach is more transparent and easier to maintain. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thanks for this it's helped me and I'm using some of what you suggested to work with some of the other suggestions posted