I have cells from D3 to D203 with numbers in random order. Some cells are empty. Is it possible to return to a cell H1 a list of missing numbers between 0 and the maximum cell value?
Welcome! Is it integers?
If so, try ListOK extension.
=LSTNOT(LSTARR(D3:D203)) will return list of skipped numbers from min to max or if you need list from 0 then use formula
=LSTDEL(LST("0-"&MAX(D3:D2003));LSTARR(D3:D203))
If you want to get the result not as a string “33-37, 42-97 …” but as values in separate cells, then wrap the formula in the LSTVAL() function
With the extension ListOk, I got exactly what I need. Thank you.
=LSTDEL(LST(“1-”&MAX(D3:D203));LSTARR(D3:D203))
Hi rsdix,
when you use LO 7.1 ore higher, you can use this formula:
=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT(“1:”&MAX(D$3:D$203)+1))-1)/(COUNTIF(D$3:D$203,ROW(INDIRECT(“1:”&MAX(D$3:D$203)+1))-1)=0),ROW(A1)),"")
You need to copy the formula into a cell and then copy it down until all the values show up. The formula here is written in English locale and uses commas as separators. If you use other separators, you have to adapt the formula. If you use the example file, LO will automatically translate the formula for you.
Filter_again.ods (18,6 KB)