How to pull the Last Non blank value based on Category - (Contract id and Month)

[jayant_mandhare]

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

[#2]

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 :)

[#3]

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!

[#4]

@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.

[#5]

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!

[#6 · Accepted solution]

@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).

[#7]

@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!

[#8]

@mtarler- Hi Matt did you get any chance to look at the final request I have written in the previous message. That would be the final piece for my formula to work properly.

[#9]

@jayant_mandhare  sorry for the delay.  the job that pays the bills was busy, lol.  In any case here is a formula to pull the first value using structured refs:

=INDEX(Table1[changes.maximum_dollar_amount.old],MIN(IF(ISNUMBER(Table1[changes.maximum_dollar_amount.old])*(Table1[contract_id]=[@[contract_id]]),ROW(Table1[changes.maximum_dollar_amount.old]),""))-1)

note that with these formulas I'm assuming the table is at the top of the page with 1 header row and then the data and hence the "-1" at the end of the row index to account for the header in row 1.