January 23rd, 2013, 11:12 am
QuoteOriginally posted by: bearishQuoteOriginally posted by: edouardQuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: `=IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!I agree that it is ugly, but it doesn't actually seem to work. It will produce a list of the initial consecutive years in which you have data, but blank out everything after the first year of missing data.you might be right (the +1 stuff). that works with the series i have as there actually is no year missing.