Forum Discussion
How to pull the Last Non blank value based on Category - (Contract id and Month)
Hello,
I am currently working on this project where I need to pull data from a `Master Data Changes` sheet into an `Analysis` sheet based on contract ID. In the `Master Data Changes` sheet I have this Max Dollar amount old (Column L) and Max dollar new column (Column M) which showcase when the user changed the dollar amount to which value. I want to pull the last nonblank of column M of `Master data change sheet ` value into `column R` of `Analysis` sheet. Logic of this formula I am planning to work out is - If a particular contract in the analysis sheet if there is no change in the master data for that month in that case I need to pull the previous month's new value(column M) for the same contract. Right now I am not able to pull the last non-blank value of the previous month because there are some blank values in the same month that I can`t delete as those are present because there are other columns are present along with it. Note: Column R of the Analysis sheet has the formula which I am originally planning but it's not working as I planned. I have copy-pasted the part of this formula that needs to be worked upon in column S of the analysis sheet. I am getting the values of the first month's first occurence. I need the last nonblank value. In the sample file in column r of the `Analysis` sheet, you will see it has picked up value 5 (m4 cell) instead of 6 (m6 cell).
I hope I am clear with my query! Any help would be really appreciated!
Regards,
JM
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).
8 Replies
- OK I really got lost in your formula so instead of trying to parse out what you are doing here is my formula to find the last non-blank value in column M of the prior month:
=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.
