VB code required to categorise bank payments

[redspanna]

Hey all

Is there someone that can offer help with some VB code to do following task...

Here is some sample data from a banking statement

Book1
	A	B	C	D
1	01-Mar-22	McDonalds	15.47	Take-Away
2	02-Mar-22	Esso	25	Fuel
3	02-Mar-22	ABC Council	150	Benefit
4	03-Mar-22	Sainsburys	135	Grocery
5	04-Mar-22	High Street Cinema	25	Activity
6	05-Mar-22	Pockit	10	Gift
7	05-Mar-22	BP Fuel	10	Fuel
8	06-Mar-22	Iceland	19	Grocery
9	07-Mar-22	Work	1500	Salary
10	08-Mar-22	State Tax	35	Benefit
Sheet1

Can the code look down each item in column B and give it a catagory in column D

For example if B1 holds something like Mcdonalds or Pizza Hut or Burger King or any other possible match that I could add, then the category Take Away is added to D1
further example is in B3 and B10 this item is recognised as a Benefit so that is added to D3 and D10

I will add all the possible category in the code but just need some help to get started - maybe something like....

If B1 equals to Mcdonalds,Pizza Hut, Burger King, Donkey , Sim Dong (plus others) then the category field for D1 would be Take Away
ELSE
If B1 equals to Shell, BP, ESSO, Total, First Garage (plus others) then the category field for D1 would be Fuel
ELSE
If B1 equals to ABC Council, Council ABC Payments, State Tax (plus others) then the category field for D1 would be Benefit

If no match found then leave D1 blank and move onto next cell in column B


Is this possible?? - any help much appreciated


thanks in advance

 

[Joe4]

I would recommend creating a lookup table with all your possible values (that you currently show in column B) in one column, and the associated category in the next column.
Then you use a simple VLOOKUP formula to return the Category, kind of like what I am showing someone else in another thread right now (see: filling cell with a specific text if another cell contains any of the specific keywords).

The advantage to using a VLOOKUP (or XLOOKUP or INDEX/MATCH) formula over a long IF formula or VBA is it is dynamic, and you can easily add/remove items to your list without having to rewrite any formulas or VBA code each time.

 

[redspanna]

I would recommend creating a lookup table with all your possible values (that you currently show in column B) in one column, and the associated category in the next column.
Then you use a simple VLOOKUP formula to return the Category, kind of like what I am showing someone else in another thread right now (see: filling cell with a specific text if another cell contains any of the specific keywords).

The advantage to using a VLOOKUP (or XLOOKUP or INDEX/MATCH) formula over a long IF formula or VBA is it is dynamic, and you can easily add/remove items to your list without having to rewrite any formulas or VBA code each time.

Thanks @Joe4 , I was thinking along those lines as I was waiting for a reply here!
I apprecite your advive and I'll check out your other post to help along the way

Many thanks

 

[gordsky]

The above is prob the best solution but if you wanted to go the formula route you could use (obvioulsy you need to add all catergories) and the down side is that if the categories change you need to alter the formula each time

=IF(OR(B1={"McDonalds","Pizza Hut"}),"Take Away",IF(OR(B1={"Shell","BP"}),"Fuel",IF(OR(B1={"ABC Council","State Tax"}),"Benefit","")))

 

[redspanna]

The above is prob the best solution but if you wanted to go the formula route you could use (obvioulsy you need to add all catergories) and the down side is that if the categories change you need to alter the formula each time

=IF(OR(B1={"McDonalds","Pizza Hut"}),"Take Away",IF(OR(B1={"Shell","BP"}),"Fuel",IF(OR(B1={"ABC Council","State Tax"}),"Benefit","")))

Thanks @gordsky

 

[Joe4]

Yeah, the "nature of the beast" suggests that you will probably need to be adding records quite a bit, as it appears to be a bank or credit card statement, and there is no limit to the number of different merchants you may use it at.

So I would strongly consider a solution that allows you to easily add more records without having to rewrite a bunch of formulas or code.