I have been trying to design a template to standardize account reconciliation in a way that even excel novices could complete the process without too much instruction. There are 7 elements to the “account numbers” we use which allows us to group or split out sub accounts as needed. The way I have set up the template involves listing the CFS element then choosing match or ignore in the adjacent cell (as seen below) then using an unholy amount of IF statements. I feel like I have chosen the least effective way of setting this up, plus it required me to input macro-assigned buttons to add 0’s to the relevant blank columns on the reference sheets, increasing the file size even further. Is there a more efficient way to go about this process to keep my file size down and avoid needing all my RAM to work in this workbook? I will note that any given file could have 20+ different iterations of the Template sheet with various combinations of the CFS elements, so I know the current version I’ve made is a recipe for future frustration. File is attached for anyone who wants to be disturbed looking at my 540+ character long formulas. 

OrgPBH60000IGNOREFundSPNSRIGNOREFunction13IGNOREEntity0IGNORESource0IGNOREPurpose0IGNOREProject20000957MATCH

​I have been trying to design a template to standardize account reconciliation in a way that even excel novices could complete the process without too much instruction. There are 7 elements to the “account numbers” we use which allows us to group or split out sub accounts as needed. The way I have set up the template involves listing the CFS element then choosing match or ignore in the adjacent cell (as seen below) then using an unholy amount of IF statements. I feel like I have chosen the least effective way of setting this up, plus it required me to input macro-assigned buttons to add 0’s to the relevant blank columns on the reference sheets, increasing the file size even further. Is there a more efficient way to go about this process to keep my file size down and avoid needing all my RAM to work in this workbook? I will note that any given file could have 20+ different iterations of the Template sheet with various combinations of the CFS elements, so I know the current version I’ve made is a recipe for future frustration. File is attached for anyone who wants to be disturbed looking at my 540+ character long formulas. OrgPBH60000IGNOREFundSPNSRIGNOREFunction13IGNOREEntity0IGNORESource0IGNOREPurpose0IGNOREProject20000957MATCH  Read More

​