Forum Discussion
Filter or Formula for second to last cel
Dear all,
I have tried to find a solution for my challenge, however I haven't managed to find the right answer.
In an Excel sheet, I have multiple rows with data divided over different amounts of columns. The only thing these rows have in common, is that the second to last filled cell in each row has a date (dd/mm/yyyy or dd-mm-yyyy).
I need a formula that shows this date. In orther words, I would need a formula that copies the second to last filled cell of each row into a column.
To visualize my question: looking at the table below; is there a formula (or other function) that automatically copies the dates from the yellow cells into the first (yellow) column?
Many Thanks!
=LOOKUP(9^9,B1:M1)Did I mention that your data has a bad structure?
In fact I dare to say it has no strucure at all.
4 Replies
A method that will return the penultimate field of each record, irrespective of its content, might be
= BYROW(data, LAMBDA(record, CHOOSECOLS(FILTER(record, record<>""),-2)))It takes each record in turn and removes the blanks to the right. Once done, it takes the second rightmost field and returns the results as a column array.
For example in A2:
=LOOKUP(1000000, B2:XFD2)
In Dutch:
=ZOEKEN(1000000; B2:XFD2)
Format A2 as a date, then fill down.
=LOOKUP(9^9,B1:M1)Did I mention that your data has a bad structure?
In fact I dare to say it has no strucure at all.
Detlef_Lewin Thank you for your help! Seems like your formula works perfectly!
I know it seems there is no structure; that's why I needed this formula 🙂
thanks for your help!
