Question about categorizing transactions from bank statement in EXCEL
I have a problem of categorizing every transaction in the bank statement for bookkeeping purposes.
As shown below, I have to input the Description for every item, and every item has to refer to different columns.
For example, if we see “CHECK NO” in REf 2, we have to input “*Look up in other excel file*”.
If “John” appears in REF2, we have to input “JOHN CO. -BC”.
If “BOOKTRANSFER” appears in “BANK TRAN. DESCRIPTION“, we have to input “BOOKTRANSFER DR. ABC CR. Def“.
If 360.9(DEBIT) and PAY TO CAR. CO(REF4) appears at the same time, than “VEHICLE LOAN – TOYOTA 123” (I still cannot figure out how to write this rule).
There are hundreds of these rules.
I wrote a function, like
=IFS(ISNUMBER(SEARCH((“BOOKTRANSFER”),CONCAT(B6,F6:K6),1)),”BOOKTRANSFER DR.ABC CR. Def”,
ISNUMBER(SEARCH((“John”),CONCAT(B6,F6:K6),1)),”JOHN CO. – BC”,
ISNUMBER(SEARCH((“CHECK NO”),CONCAT(B6,F6:K6),1)),”*Look up in other excel file*”),
and drag it down in cell E, to facilitate my job.
However, I cannot enter every single rules into a cell, and it would be very long and complicated. It is also difficult to modify.
I just wonder whether I can do it with a table or not, like this:
so I can input hundreds of rules.
But I still cannot figure out how to do it with a table.
Please advise if there is a smarter way to do the job.
Thank you.
(P.S. Reply in Chinese and English are welcomed)
I have a problem of categorizing every transaction in the bank statement for bookkeeping purposes. As shown below, I have to input the Description for every item, and every item has to refer to different columns. For example, if we see “CHECK NO” in REf 2, we have to input “*Look up in other excel file*”.If “John” appears in REF2, we have to input “JOHN CO. -BC”.If “BOOKTRANSFER” appears in “BANK TRAN. DESCRIPTION”, we have to input “BOOKTRANSFER DR. ABC CR. Def”.If 360.9(DEBIT) and PAY TO CAR. CO(REF4) appears at the same time, than “VEHICLE LOAN – TOYOTA 123” (I still cannot figure out how to write this rule). There are hundreds of these rules. I wrote a function, like=IFS(ISNUMBER(SEARCH((“BOOKTRANSFER”),CONCAT(B6,F6:K6),1)),”BOOKTRANSFER DR.ABC CR. Def”,ISNUMBER(SEARCH((“John”),CONCAT(B6,F6:K6),1)),”JOHN CO. – BC”,ISNUMBER(SEARCH((“CHECK NO”),CONCAT(B6,F6:K6),1)),”*Look up in other excel file*”),and drag it down in cell E, to facilitate my job. However, I cannot enter every single rules into a cell, and it would be very long and complicated. It is also difficult to modify. I just wonder whether I can do it with a table or not, like this: so I can input hundreds of rules.But I still cannot figure out how to do it with a table.Please advise if there is a smarter way to do the job.Thank you. (P.S. Reply in Chinese and English are welcomed) Read More