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).
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).
mtarler- thank you so much for your help! Really appreciate it! it's amazing to see the logic is working fine right now. I would like to ask one final favor from you. I need to do a similar thing with the `L` column of the `Master data changes` sheet. From this column, I need to pick up the first non-blank value by contract id eg: (L2 cell for contract 11930/ L4 cell for contract 11935) instead of the last nonblank value which you did in the previous version. There could be a possibility the first value would be blank so we need to move below that and check if it has any nonblank value. If it has 0 or any other amount I need to pull that value. It would be great if you could provide fox for this.? Once I get this part of the formula I would be able to incorporate both of these foxes in my original formula. Let me know if you have any queries. Thanks!