Forum Discussion
Tracking Highest Revisions with Duplicated Titles
I'm looking to track the highest revision for a transmittal / document log.
Each revision of the file title received is entered in it's own row - If "Structural Drawing 1" (col. A) has seven revisions, there are seven rows for that file and a column for revision number (col. B). See example below.
I would like "Old" in col. C for every row that is a previous revision. Drawings of the highest revision would say "IFC" in col. C. This would allow for easy sorting.
| Drawing Title | Version # | Status |
| Structural Drawing 1 | 1 | Old |
| Structural Drawing 1 | 2 | Old |
| Structural Drawing 1 | 3 | Old |
| Structural Drawing 1 | 4 | Old |
| Structural Drawing 1 | 5 | Old |
| Structural Drawing 1 | 6 | Old |
| Structural Drawing 1 | 7 | IFC |
| Schedule | 1 | IFC |
| Specifications | 1 | IFC |
| Safety Plan | 1 | Old |
| Safety Plan | 2 | IFC |
Please let me know if this a solution. If not, please recommend an alternative solution.
Thanks,
In C3:
=LET(Title, A3:A13, Version, B3:B13, IF(Version=MAXIFS(Version, Title, Title), "IFC", "Old"))
Adjust the ranges as needed. See the attached demo workbook.
4 Replies
In C3:
=LET(Title, A3:A13, Version, B3:B13, IF(Version=MAXIFS(Version, Title, Title), "IFC", "Old"))
Adjust the ranges as needed. See the attached demo workbook.
Thank you Hans!!!
Structured reference (Tables) (to make it a table select the data and choose Home->Format as Table)
=IF([@Version]=MAX(FILTER( [Version],[Drawing Title]=[@[Drawing Title]])), "IFC", "Old")
or range references
=IF($B$2:$B$100=BYROW($A$2:$A$100,LAMBDA(r, MAX(FILTER( $B$2:$B$100, $A$2:$A$100=r)))), "IFC", "Old")
I didn't have a chance to verify these solutions, but that you m_tarler.
