Excel Top Tip #4 - Categorise acronym variations

Here is the email sent to all subscribers on 23 July 2020. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

Bank statements frequently refer to payments to or from the same company or individual using a variety of different acronyms. You might also find yourself in need of classifying many payees into a single group, like ‘travel’.  

This can get frustrating when constantly filtering by a new keyword that you missed, or you end up resorting to a never ending nested IF statement, both of which have low limits in Excel for the number of variations that can be incorporated.

This can be a particular issue for insolvency investigations when finding payments to HM Revenue & Customs (HMRC).

With our Excel Top Tip, you can solve this problem and categorise bank transactions like a pro.

Top Tip #4 – Categorise acronym variations and multiple payees

By using this formula structure, you’ll be able to find payees with different keywords in the transaction narrative with a robust, replicable, readable advanced process that'll impress your colleagues:

☝️Your handy keyboard shortcut: Ctrl + D to bookmark this page

And here is an example that is particularly useful for returning true or false against a list of 100s or 1000s of transactions when the narrative matches one of the many acronyms for HMRC (or your country’s tax office). 

=SUMPRODUCT( --ISNUMBER(SEARCH(" "&{"HMRC","HM","Rev&Cus","VAT","PAYE“,"RCus","NIC","NatIns","Ninsurance"}&" "," "&INDIRECT("RC[-1]",0)&" ")) )>0

However, it has many other uses which we’d love to see you explore and share with us. Let us know how you get on!

Thank you,

The StatementReader Team