I have a range of cells containing values in no order. I want to scan back up (or down) the list and find the first cell value greater than the reference cell.
IE a1:a10 contains 3,7,12,2,5,8,24,32,6,3 and a11 contains 15. I want to find the first cell in the range a1:a10 has a value greater than the value in a11. So if searching ascending I want a8 and if descending a7.
Do you use “ascending” for upwards (starting at the bottom element of the column) and “descending” for downwards?
Yes, in that instance.
Please see below - I have attached the complete macro that does what I (think!) I want…
Hallo
=INDEX(FILTER(A1:A10;A1:A10>A11);1) #for the first value >A11
=LET(x;FILTER(A1:A10;A1:A10>A11);INDEX(x;COUNT(x))) # for the last value >A11
both Formulas for LO24.8 or later!!
1 Like
Thanks for that. But I’m on 24.2.7.2 - apologies - I should have stated that in my initial post.
=INDEX(A1:A10;MINIFS(ROW(A1:A10);A1:A10;">"&A11)) #first …
=INDEX(A1:A10;MAXIFS(ROW(A1:A10);A1:A10;">"&A11)) #last …
3 Likes
Thanks for the input, but I get a ‘VALUE!’ error from that. My actual code is:
=INDEX(B93:B267,MINIFS(ROW(B93:B267),B93:B267,">"&B268))
…MINIFS(ROW(B1:B175),B93:B267,">"&B268))
In this latter form, it would become difficult to keep the number of rows in sync. Despite looking more complex, this form could be a bit simpler in maintenance:
MINIFS(ROW(B93:B267)-ROW(B93)+1;B93:B267;">"&B268))I’m doing something wrong, as I’m getting either Value errors or zero. Thanks though both for your help. I’ll play around a bit now you’ve given me some pointers.
I have written a macro to do what I want (took me a while). The code seems to work APART from the formatting of the output cells.
The macro runs over a column and checks if the value in that column has a bigger or smaller value in the past - and if so posts when tjose events happened and what the values were. I want the macro to revisit the whole colum each time it is run in case I have manually changed any values. So the macro needs to blank values where it may have posted values in the past. It does that OK - BUT - the blank values are being shown as £0.00 and an 1899 date. Is there a way to reset a cell to nothing? My code for the posting bit is:
' We have the current row and which row then has a bigger value and a smaller value.....so now post into d/e bigger date and value and f/g smaller date and value
SUB postValues(p_currSheet,p_currRow, p_dateCol,p_valCol, p_foundbigger,p_foundsmaller)
DIM l_biggerCol1 AS INTEGER
DIM l_smallerCol1 AS INTEGER
DIM l_biggerCell AS OBJECT
DIM l_biggerVal1 AS STRING
DIM l_biggerVal2 AS STRING
DIM l_smallerCell AS OBJECT
DIM l_smallerVal1 AS STRING
DIM l_smallerVal2 AS STRING
DIM l_destCell AS OBJECT
' l_bigger.......
' Date column.....
IF p_foundBigger THEN
l_biggerCell = p_currSheet.getCellByPosition(p_dateCol,p_foundBigger)
l_biggerVal1 = l_biggerCell.VALUE
' Value Column
l_biggerCell = p_currSheet.getCellByPosition(p_valCol,p_foundBigger)
l_biggerVal2 = l_biggerCell.VALUE
ELSE
l_biggerVal1 = ""
L_biggerVal2 = ""
END IF
l_destCell = p_currSheet.getCellByPosition(p_dateCol+3,p_currRow)
l_destCell.setValue(l_biggerVal1)
l_destCell = p_currSheet.getCellByPosition(p_valCol+3,p_currRow)
l_destCell.setValue(l_biggerVal2)
' l_smaller.......as bigger
IF p_foundSmaller THEN
l_smallerCell = p_currSheet.getCellByPosition(p_dateCol,p_foundSmaller)
l_smallerVal1 = l_smallerCell.VALUE
l_smallerCell = p_currSheet.getCellByPosition(p_valCol,p_foundSmaller)
l_smallerVal2 = l_smallerCell.VALUE
ELSE
l_smallerVal1 = ""
L_smallerVal2 = ""
END IF
l_destCell = p_currSheet.getCellByPosition(p_dateCol+5,p_currRow)
l_destCell.setValue(l_smallerVal1)
l_destCell = p_currSheet.getCellByPosition(p_valCol+5,p_currRow)
l_destCell.setValue(l_smallerVal2)
MSGBOX "Posted Bigger Val1 (" & l_biggerVal1 & "} Val2 (" & l_biggerVal2 & ") Smaller Val1 (" & l_smallerVal1 & ") Val2 (" & l_smallerVal2 & ") Into (" & p_dateCol+3 & ") (" & p_valCol+3 & ") (" & p_dateCol+5 & ") (" & p_valCol+5 & ")"
END SUB 'postValues
…the “” gets posted, but I want really a l_destCell.unSetValue(). Is there such a thing ?
[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]
This is the full macro. It assumes two columns are populated - A and B - column A had dates in ascending order and column B has a set of random values. The macro then looks back up column A to find the last populated row - then looks at column B , and using the last value in that column populates columns D/E and F/G. Column D is the last time (ie the date from col A ) the Value in B was bigger than the last value in B, and Col E is then the value that exceeded the last value - columns F and G are similar but for smaller values (ie when was the last date that the value was smaller than the last entry in Col B)
The code follows.
' ***** BASIC *****
TYPE t_found
bigger AS INTEGER
smaller AS INTEGER
END TYPE
' What I want to do here is to take a value from the current cell and go back over a rang of cells and find the last time
' the range had a value bigger (or smaller) than the current cell
' This is for pension growth ioe the current value is £10 and it was £13 two days back (or it was £7 five days ago)
SUB Main
DIM l_sheetName AS STRING
DIM l_StartRow AS INTEGER
DIM l_EndRow AS INTEGER
DIM l_currSheet AS OBJECT
DIM l_dateCol AS INTEGER
DIM l_valCol AS INTEGER
DIM l_currRow AS INTEGER
DIM m_found AS NEW t_found
' NB the top lef is 0,0 NOT 1,1 so col A is 0, B is 1 etc - ditto rows - Row 1 is 0, 2 is 1.. Why?
' whay don't they adjust that in the functions?
l_sheetName = "Pension Growth"
l_dateCol = 0
l_valCol = 1
l_EndRow = 93
l_StartRow = 5000
'l_currSheet = ThisComponent.getCurrentController.activeSheet
l_currSheet = ThisComponent.getCurrentController.activeSheet
IF (l_currSheet.name <> l_sheetName) THEN
MSGBOX "Wrong Sheet (" & l_currSheet.name & ") - Only Works On (" & l_sheetName & ")"
ELSE
MSGBOX l_currSheet.name
' StartRow is initially set to 5000 ( roughly 10 years of data) and gets modified by getMaxRow
' checkCol is A (0) - see which cols have dates and values (col B = 1)
l_StartRow = getMaxRow(l_currSheet,l_dateCol,l_StartRow,l_EndRow)
FOR l_currRow = l_StartRow TO l_EndRow STEP - 1
m_found = findRows(l_currSheet,l_currRow,l_valCol,l_StartRow,l_EndRow)
' Now use m_found to populate cols d (3) as bigger date, e (4) as bigger value, f(5) as smaller date and g (6) as smaller value
postValues(l_currSheet,l_currRow,l_dateCol,l_valCol,m_found.bigger,m_found.smaller)
NEXT l_currRow
END IF
END SUB 'Main
' Get the max row.. this is the start point - start looking 5000 rows down and come back up the sheet until a non blank date (col 0 - ie A)
' Returns the max row where a date and value is present
SUB getMaxRow(p_currSheet,p_dateCol,p_StartRow,p_EndRow) AS INTEGER
DIM l_row AS INTEGER
DIM l_cell AS OBJECT
MSGBOX "StartRow (" & p_StartRow & ") LowRow (" & p_EndRow & ") Col (" & p_dateCol & ")"
FOR l_row = p_StartRow TO p_EndRow STEP -1
IF p_currSheet.getCellByPosition(p_dateCol,l_row).TYPE <> 0 THEN
IF p_currSheet.getCellByPosition(p_dateCol +1,l_row).TYPE <> 0 THEN
EXIT FOR
END IF
END IF
NEXT l_row
MSGBOX "Got Start Row Of (" & l_row & ") Cell Value (" & p_currSheet.getCellByPosition(p_dateCol,l_row).VALUE & ")"
getMaxRow = l_row
END SUB ' getMaxRow
' Find the next row (going up) that has a bigger or smaller value than the current row
SUB findRows(p_currSheet,p_currRow,p_valCol,p_StartRow,p_EndRow) AS t_found
DIM l_currRow AS INTEGER
DIM l_currCell AS OBJECT
DIM l_currCellValue AS STRING
DIM l_astr AS STRING
DIM l_myStr AS STRING
DIM l_found AS NEW t_found
l_found.bigger = 0
l_found.smaller = 0
l_checkValue = p_currSheet.getCellByPosition(p_valCol,p_currRow).value
FOR l_currRow = p_currRow TO p_EndRow STEP -1
l_currCellValue = p_currSheet.getCellByPosition(p_valCol,l_currRow).value
IF l_found.bigger THEN
ELSE
IF l_currCellValue > l_checkValue THEN
l_found.bigger = l_currRow
END IF
END IF
IF l_found.smaller THEN
ELSE
IF l_currCellValue < l_checkValue THEN
l_found.smaller = l_currRow
END IF
END IF
IF l_found.bigger AND l_found.smaller THEN
EXIT FOR
END IF
NEXT l_currRow
' l_myStr = l_myStr & " Check Value ( " & l_checkValue & ") Found Bigger In (" & l_found.bigger & ") Smaller In (" & l_found.smaller & ")"
' MSGBOX l_myStr
findRows = l_found
END SUB ' -- findRows
' We have the current row and which row then has a bigger value and a smaller value.....so now post into d/e bigger date and value and f/g smaller date and value
SUB postValues(p_currSheet,p_currRow, p_dateCol,p_valCol, p_foundbigger,p_foundsmaller)
DIM l_biggerCol1 AS INTEGER
DIM l_smallerCol1 AS INTEGER
DIM l_biggerCell AS OBJECT
DIM l_biggerVal1 AS STRING
DIM l_biggerVal2 AS STRING
DIM l_smallerCell AS OBJECT
DIM l_smallerVal1 AS STRING
DIM l_smallerVal2 AS STRING
DIM l_destCell AS OBJECT
' l_bigger.......
IF p_foundBigger THEN
' Date column.....
l_biggerCell = p_currSheet.getCellByPosition(p_dateCol,p_foundBigger)
l_biggerVal1 = l_biggerCell.VALUE
' Value Column
l_biggerCell = p_currSheet.getCellByPosition(p_valCol,p_foundBigger)
l_biggerVal2 = l_biggerCell.VALUE
ELSE
l_biggerVal1 = ""
L_biggerVal2 = ""
END IF
l_destCell = p_currSheet.getCellByPosition(p_dateCol+3,p_currRow)
l_destCell.setValue(l_biggerVal1)
l_destCell = p_currSheet.getCellByPosition(p_valCol+3,p_currRow)
l_destCell.setValue(l_biggerVal2)
' l_smaller.......as bigger
IF p_foundSmaller THEN
l_smallerCell = p_currSheet.getCellByPosition(p_dateCol,p_foundSmaller)
l_smallerVal1 = l_smallerCell.VALUE
l_smallerCell = p_currSheet.getCellByPosition(p_valCol,p_foundSmaller)
l_smallerVal2 = l_smallerCell.VALUE
ELSE
l_smallerVal1 = ""
L_smallerVal2 = ""
END IF
l_destCell = p_currSheet.getCellByPosition(p_dateCol+5,p_currRow)
l_destCell.setValue(l_smallerVal1)
l_destCell = p_currSheet.getCellByPosition(p_valCol+5,p_currRow)
l_destCell.setValue(l_smallerVal2)
' MSGBOX "Posted Bigger Val1 (" & l_biggerVal1 & "} Val2 (" & l_biggerVal2 & ") Smaller Val1 (" & l_smallerVal1 & ") Val2 (" & l_smallerVal2 & ") Into (" & p_dateCol+3 & ") (" & p_valCol+3 & ") (" & p_dateCol+5 & ") (" & p_valCol+5 & ")"
END SUB 'postValues
…the only problem (I think) I have is when the values change and I need to blank a value. I get an 1899 date and 0 in the D/E,F/G columns and I really want them to be blank. It’s probably an error in the code that I haven’t spotted.
[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]
OK - fixed that final issue. The code uses setValue even when I’m trying to ‘blank’ the cell. That then gives me the 1899 date and 0.00 value - so I thought I’d record a macro of what hitting delete does in a cell - and that showed me a call to a uno method of clearContents. So I googled a bit more and that clearContents method is then available in my case - so I used this type of code:
…IF l_smallerVal1 = "" THEN l_destCell.clearContents(7) ELSE l_destCell.setValue(l_smallerVal1) END IF…
…and that works fine. So if there are no bigger or smaller values I now get blanks in the cells rather than 1899 and 0.00.
If anyone wants the full macro, let me know and I’ll post the whole thing.