Forum Discussion
Solved
Find latest iteration of reference number
Hi all,
Hoping you can help.
I have a spreadsheet that tracks document reviews. I have been asked if there is a way we can track the documents that have had an initial review and then sent back to our customer to make changes etc.
These reviews are assigned a reference number in column H and the different iterations of this in column I. So one reference number can appear multiple times.
So I need a formula to look at the reference number then find the latest iteration then look in column AK to check the category if its reached a 1 then I need it to mark all version of that reference as accepted and if there's a 0. 2. or 3 then I need it to return Closed.
Not sure if this can be done in one formula or if another will be needed to first find the latest iteration and then another for the Accepted or Closed.
Hope this all makes sense.
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.
- =IF(AND(XLOOKUP($A13,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300),XLOOKUP($A13,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300)),XLOOKUP($A13,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300,'FR-105-RCB Checker_MEFA '!$B$9:$C$3300,0),XLOOKUP($A13,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300,' FR-104-RCB Checker_Info Board'!$B$10:$C$3300,0))
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.
Hi Peter,
Thanks for the feedback. I've been playing about with a few things to try and get the Closed or Accepted to only appear on the latest DRN iteration row. I've got this work using a cell range Column BB, but when I try to use structured references, it doesn't seem to work BA.
My reasoning is that I want to calculate any DRN with our client that hasn't been returned based on the latest version we sent them. Just wondering if I'm missing something simple. I'm having the same issue with column AY it works with Cell ranges but not structured references.
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
