CODEWITHSUNDEEP
excel-formula
knightridar
knightridar

Reputation: 11

Is there an excel formula to extract numbers from the last 25 rows in a column with mixed data?

Is there an excel formula to extract numbers from the last 25 rows in a column with mixed data?

I want to place those numbers in another spreadsheet within the same workbook.

The rows have either a decimal number or N/A

Here is the data table :

extract numbers

Upvotes: 0

Views: 75

Answers (1)

user4039065
user4039065

Reputation:

To get the last 25 numbers or text from Sheet3!A:A put this at the top of an unused column and fill down 25 rows.

=INDEX(Sheet3!A:A, MAX(IFERROR(MATCH(1E+99, Sheet3!A:A), 0), IFERROR(MATCH("zzz", Sheet3!A:A), 0))-25+ROW(1:1))

For the last 25 true numbers discarding text and errors,

'last 25 numbers, last number first
=INDEX(B:B, AGGREGATE(14, 7, ROW(B$2:INDEX(B:B, MATCH(1E+99, B:B)))/ISNUMBER(B$2:INDEX(B:B, MATCH(1E+99, B:B))), ROW(1:1)))
'last 25 numbers, top row number first
=INDEX(B:B, AGGREGATE(14, 7, ROW(B$2:INDEX(B:B, MATCH(1E+99, B:B)))/ISNUMBER(B$2:INDEX(B:B, MATCH(1E+99, B:B))), ABS(ROW(1:1)-26)))

Upvotes: 1

Related Questions