returning value from large table

[Richardeg]

I import an extract from a database and am looking for updates in comments
The problems are many
All the columns in the extrract are called Comments
The problem is that they currently run from Columns LU to ON in the extract
The rows only have data in them if there is an update
But for each comment it adds in in the next column, and if the comments keep adding it would be then 00, OP etc

I only want returned the first 11 characters of each column ie 08/SEP/2020. It is always 11 and the date

So basically I want to look up anything that is in any of the columns called Comments and then go to the last cell that has a comment in it and return the first 11 characters. or

So if in row 1 the comment is in column LU it would find the first and only comment and return the 11 characters
If in row 2 the comment is in MN (meaning there had been multiple comments added) then it would go all the way through the columns to get the last one and then return the date
If however another comment column was added as there are too many comments, it would still identify it in the column call comments albeit now OP, OQ etc and still return
So maybe it is go to the comments columns from Right to left and if blank keep going left.
I don't know how or if that is even possible
Any thoughts?

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, consider giving us a small set of sample data and expected results with XL2BB so we have realistic data to test with and don't have to type it out manually ourselves. You will get more helpers (and faster) that way.

Could you make use of something like this?

20 08 14.xlsm
	LT	LU	LV	LW	LX
1		Comments	Comments	Comments	Comments
2	08/SEP/2020	08/SEP/2020 Some comment
3	05/12/2019	15/APR/2019 Begin	25/JUN/2019 Comment 2	05/12/2019 Comment 3
Last Comment Date

Cell Formulas
Range	Formula
LT2:LT3	LT2	=LEFT(LOOKUP("z",LU2:ZZ2),11)

 

[Richardeg]

OMG fantastic.
Some of the columns are blank so how would I combine an IFERROR formula or get rid of the N/A

 

[Peter_SSs]

how would I ... get rid of the N/A

=IFNA(LEFT(LOOKUP("z",LU3:ZZ3),11),"")

 

[Richardeg]

fantastic and that worked

 

[Peter_SSs]

Glad it helped. Thanks for the confirmation,

 

[Richardeg]

ok, so....It is all working, but now when I apply the formula to count date ranges, because the comment data is text, not sort of formatting will allow me to change it to a dd/mmm/yy format
any suggestions?

 

[Peter_SSs]

If you want actual dates returned, try =IFNA(LEFT(LOOKUP("z",LU2:ZZ2),11)+0,"")
and format the column with the date format you want.

 

[Richardeg]

If I wasn't in Melbourne, and locked down, I'd give you a big hug

 

[Peter_SSs]

You're welcome.

Hope you are staying safe down there and that things improve in Melbourne soon!!