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