Forum Discussion
How to pull the Last Non blank value based on Category - (Contract id and Month)
jayant_mandhare You're right I didn't know. I have added that as a criteria in this version. I also formatted both tables as tables and gave a structured reference version because that will limit the comparisons to only the table area and the tables will grow with the data (i.e. more efficient method).
=IFERROR(INDEX('Master Data Changes'!M:M,1/(1/MAX(ROW('Master Data Changes'!M:M)*ISNUMBER('Master Data Changes'!M:M)*IFERROR((I2-1)=EOMONTH(DATEVALUE('Master Data Changes'!I:I&" "&'Master Data Changes'!H:H),0),FALSE)))),0)
That said, your calculation / expected value appears to be wrong as the M4 - M6 cells are from NOV not DEC
I am filtering for LAST month based on: IFERROR((I2-1)=EOMONTH(DATEVALUE('Master Data Changes'!I:I&" "&'Master Data Changes'!H:H),0)
which uses column I (period start) - 1 to get last day of the previous month and compare with End Of Month based on the month and year. It is rather clunky and would be nice if you just had an actual date to use instead but I was working with what you have 🙂
Hey mtarler, Thanks for your quick response! After looking at your solution I believe there is a misunderstanding due to my explanation. When I copy-pasted your formula above in the Analysis sheet I am getting zero value instead of 6 (M6 Cell) which is the last non-blank value in Column M for that particular contract 11935. I believe your formula is pulling the December month data which is completely blank that I don`t need. The reason I have taken blank December month is that there is a possibility the previous month is completely blank but the month before that i.e. November month has some non-blank value in column M which I need to pull. Also, one more thing you might have observed in November month there is a specific blank row I have inserted intentionally `November 4`. Because there is the possibility of having that in real master data. Thus we need to pull specifically the last nonblank value before the current month which is 6 in this sample data at November 3 Line. I hope I have made my problem clearer. Looking forward to your response!
jayant_mandhare so am i understanding that you want the last value before this month. So all I had to do was tweak the last part of the formula to be <= instead of =. I am attaching the updated version and I hope this is what you meant/need.
Hi mtarler, thanks for your quick response! After validating the formula with the Another contract id the formula it is not working as required. When I inserted new contract `11930` transaction record in the analysis sheet it still picked up the value of the `11935` contract. After going through your formula it seems you just considered only month & year for reference but contract id (column E of Analysis sheet) is a key column based on which I need to check the `master data changes` is missing. In this analysis dataset, there could be multiple contracts and every contract needs to be treated as per their own master data. In the file attached you will see in the analysis sheet in S8 cell, I need the m3 value of the master data sheet .i.e 4 but it's picking up 8 right now. Please email me if you have more questions we could have quick chat over there. my email id- jayantmandhare@gmail.com. Thanks in advance for your help!
jayant_mandhare You're right I didn't know. I have added that as a criteria in this version. I also formatted both tables as tables and gave a structured reference version because that will limit the comparisons to only the table area and the tables will grow with the data (i.e. more efficient method).