Forum Discussion
Show value of last cell to the left that has a value
Column A has unique references of reports.
Columns B to F show versions of those reports, version 1, version 2 etc......
in the cells of columns B to F is a number (4, 7, 2 etc)
But not all reports have the same number of versions. Some reports only have 1 version ( only a value in column B), some will have 3 versions ( values in columns B C D), etc . Newer versions of reports override older versions.
So in a new column G I only want to show the value of the latest version of that report. How to solve this,
21 Replies
Is it sufficient simply to count the number of entries on each row? If so,
= SIGN(versions<>"")
will flag each version of each document with a 1. There are very few functions that will process such a 2D array to give a column of results; the main one is MMULT. Such matrix multiplication can give the number of versions for each document
= MMULT(SIGN(versions<>""), SEQUENCE(COLUMNS(versions),,,0))
I have additional functions such as
= SUMROWS(SIGN(versions<>""))
but they are not generally available.
- I would assume the version numbers are in ascending order? If so, then perhaps this is what you want?
=MAX(B2:F2)
If that is not the case, then maybe this?
=LOOKUP(MAX(B2:F2)+1,B2:F2)- Columns of versions are in ascending order yes. But MAX gives me the highest value in any of the versions, not necesarily the value of the latest version.
Later versions can have lower values in their cell than previous versions. I dont need the number of the version, I need the value of that. Ersion of the report. The score if you want.- Did you try the second suggestion using Lookup?
