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