Help with a formula to categorize description in bank statement

[buddydubbo]

hi
i am still learning about excel (self taught) and would like help with a formula
my statement comes with date, description, amount and balance only
i have manage to add on a type using a simple IF formula but i need to make
the descriptions into a category in far column
so for example first line it would enquire if one of those words are in the cell then report it
coresponding cell under title category.
thanks if anyone can help

[TABLE="width: 1152"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]STATEMENT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ADD ON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]DESCRIPTION[/TD]
[TD]AMOUNT[/TD]
[TD]BALANCE[/TD]
[TD] [/TD]
[TD]TYPE[/TD]
[TD]CATEGORY[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]DIRECT CREDIT X SALARY[/TD]
[TD="align: right"]$805.00[/TD]
[TD="align: right"]$1,208.30[/TD]
[TD] [/TD]
[TD] Credit [/TD]
[TD]SALARY[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]VISA PURCHASE DONS COFFEE SHOP EAST HATFIELD[/TD]
[TD="align: right"]-$14.30[/TD]
[TD="align: right"]$1,194.00[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]CARD PUCHASE[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]VISA PURCHASE VOOM CHEMIST CENTRAL STATION[/TD]
[TD="align: right"]-$60.43[/TD]
[TD="align: right"]$1,133.57[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]CARD PUCHASE[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]INTERNET TRANSFER FROM 111111110 REF NO 22222222[/TD]
[TD="align: right"]-$49.80[/TD]
[TD="align: right"]$1,083.77[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]TRANSFER[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]VISA PURCHASE SUPERMARKET [/TD]
[TD="align: right"]-$116.65[/TD]
[TD="align: right"]$967.12[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]CARD PURCHASE[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]INTERNET TRANSFER CREDIT FROM 123123 [/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$1,167.12[/TD]
[TD] [/TD]
[TD] Credit [/TD]
[TD]INTERNET PURCHASE[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]EFTPOS WDL GENERAL STORE GREYFORD[/TD]
[TD="align: right"]-$67.50[/TD]
[TD="align: right"]$1,099.62[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]EFT PURCHASE[/TD]
[/TR]
[TR]
[TD]14/04/2016[/TD]
[TD]DIRECT DEBIT GVC NO 5555555555[/TD]
[TD="align: right"]-$85.40[/TD]
[TD="align: right"]$1,014.22[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]GVC[/TD]
[/TR]
[TR]
[TD]12/04/2016[/TD]
[TD]ATM OPERATOR FEE WITHDRAWAL [/TD]
[TD="align: right"]-$1.00[/TD]
[TD="align: right"]$1,013.22[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]FEE[/TD]
[/TR]
[TR]
[TD]12/04/2016[/TD]
[TD]ATM WITHDRAWAL [/TD]
[TD="align: right"]-$150.00[/TD]
[TD="align: right"]$863.22[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]MONEY WDL[/TD]
[/TR]
[TR]
[TD]12/04/2016[/TD]
[TD]LOAN REPAYMENT TO 9997999[/TD]
[TD="align: right"]-$320.00[/TD]
[TD="align: right"]$543.22[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]CAR LOAN[/TD]
[/TR]
[TR]
[TD]12/04/2016[/TD]
[TD]DIRECT DEBIT HEALTH FUND 7877878[/TD]
[TD="align: right"]-$120.00[/TD]
[TD="align: right"]$423.22[/TD]
[TD] [/TD]
[TD] Debit [/TD]
[TD]HEALTH FUND[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board! (Good to see the registration problems seem to have been resolved )

Would something like this be of use? Create a Key word <-> Category lookup table like I have in columns I:J then use the formula in G2, copied down. (Note some columns hidden)

Excel Workbook
	B	G	H	I	J
1	DESCRIPTION	CATEGORY		Keyword	Category
2	DIRECT CREDIT X SALARY	SALARY		Salary	SALARY
3	VISA PURCHASE DONS COFFEE SHOP EAST HATFIELD	CARD PUCHASE		Visa	CARD PUCHASE
4	VISA PURCHASE VOOM CHEMIST CENTRAL STATION	CARD PUCHASE		Internet	TRANSFER
5	INTERNET TRANSFER FROM 111111110 REF NO 22222222	TRANSFER		Eftpos	EFT PURCHASE
6	VISA PURCHASE SUPERMARKET	CARD PUCHASE		GVC	GVC
7	INTERNET TRANSFER CREDIT FROM 123123	TRANSFER		Fee	FEE
8	EFTPOS WDL GENERAL STORE GREYFORD	EFT PURCHASE		Atm Withdrawal	MONEY WDL
9	DIRECT DEBIT GVC NO 5555555555	GVC		Loan Repayment	CAR LOAN
10	ATM OPERATOR FEE WITHDRAWAL	FEE		Health Fund	HEALTH FUND
11	ATM WITHDRAWAL	MONEY WDL
12	LOAN REPAYMENT TO 9997999	CAR LOAN
13	DIRECT DEBIT HEALTH FUND 7877878	HEALTH FUND
14
Category

 

[buddydubbo]

Thanks much Peter
This was exactly what i was looking for i can refine it even more now.

i had worked out a solution from reading a few post and learning a bit about some formulas but it was super long and i had to separate a few categories over a few columns

was going to post for a way to shorten but you beat me to it
thanks again

 

[Peter_SSs]

Cheers. Glad it helped. Thanks for letting us know.

 

[buddydubbo]

Hi Peter,

one more question on this!
I am working on a VBA to move each category to its own sheet.
Is it possible to incorporate that in this formula?

Thanks

 

[Peter_SSs]

There would a number of approaches but having the categories in the original sheet (as per this formula) is likely to be relevant to all.
For a vba approach to then splitting into individual sheets, have a look at my posts in this thread.
If you were interested ina formula approach, then have a look here.

 

[buddydubbo]

hi peter,

thanks for the links i have been looking at the formula link and can't seem to work
out how to lay it to my table.

i made sheets for all the categories in column J (above table).
so i know on each sheet where i want the row to start i put the formula.

i want it to query column G and copy each row with that category to corresponding sheet.
no matter how i try to follow what you wrote on the above link i can't seem to get it right!

can you help me further please!
thankyou

 

[Peter_SSs]

.. looking at the formula link and can't seem to work
out how to lay it to my table.

There is a little bit of adjustment due to different columns being used & different sheet names, but it is basically the same.
So here is my Category sheet again, with column H used as the helper like column column F of post #6 in the linked thread. (And I've also fixed my spelling of PURCHASE )

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	DATE	DESCRIPTION	AMOUNT	BALANCE	TYPE		CATEGORY		Keyword	Category
2	15/04/2016	DIRECT CREDIT X SALARY	805.00	1,208.30	Credit		SALARY	SALARY|1	Salary	SALARY
3	15/04/2016	VISA PURCHASE DONS COFFEE SHOP EAST HATFIELD	-14.30	1,194.00	Debit		CARD PURCHASE	CARD PURCHASE|1	Visa	CARD PURCHASE
4	15/04/2016	VISA PURCHASE VOOM CHEMIST CENTRAL STATION	-60.43	1,133.57	Debit		CARD PURCHASE	CARD PURCHASE|2	Internet	TRANSFER
5	15/04/2016	INTERNET TRANSFER FROM 111111110 REF NO 22222222	-49.80	1,083.77	Debit		TRANSFER	TRANSFER|1	Eftpos	EFT PURCHASE
6	15/04/2016	VISA PURCHASE SUPERMARKET	-116.65	967.12	Debit		CARD PURCHASE	CARD PURCHASE|3	GVC	GVC
7	15/04/2016	INTERNET TRANSFER CREDIT FROM 123123	200.00	1,167.12	Credit		TRANSFER	TRANSFER|2	Fee	FEE
8	15/04/2016	EFTPOS WDL GENERAL STORE GREYFORD	-67.50	1,099.62	Debit		EFT PURCHASE	EFT PURCHASE|1	Atm Withdrawal	MONEY WDL
9	14/04/2016	DIRECT DEBIT GVC NO 5555555555	-85.40	1,014.22	Debit		GVC	GVC|1	Loan Repayment	CAR LOAN
10	12/04/2016	ATM OPERATOR FEE WITHDRAWAL	-1.00	1,013.22	Debit		FEE	FEE|1	Health Fund	HEALTH FUND
11	12/04/2016	ATM WITHDRAWAL	-150.00	863.22	Debit		MONEY WDL	MONEY WDL|1
12	12/04/2016	LOAN REPAYMENT TO 9997999	-320.00	543.22	Debit		CAR LOAN	CAR LOAN|1
13	12/04/2016	DIRECT DEBIT HEALTH FUND 7877878	-120.00	423.22	Debit		HEALTH FUND	HEALTH FUND|1
Category

And here is the 'Card Purchase' sheet set up similar to the 'Supplier' sheet in the other thread.
Column I could be hidden after you copy the formula well down.
Formula in B2 is copied across and down, and you then may have to format date & number columns appropriately.
Also, you may not want all columns as 'Balance' doesn't make so much sense once extracted from the main sheet.
You can make other sheets similar to this for the other categories.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Category	DATE	DESCRIPTION	AMOUNT	BALANCE	TYPE
2	Card Purchase	15/04/2016	VISA PURCHASE DONS COFFEE SHOP EAST HATFIELD	-14.30	1,194.00	Debit			3
3		15/04/2016	VISA PURCHASE VOOM CHEMIST CENTRAL STATION	-60.43	1,133.57	Debit			4
4		15/04/2016	VISA PURCHASE SUPERMARKET	-116.65	967.12	Debit			6
5									#N/A
6									#N/A
Card Purchase