Using search function to classify data

[Fanny]

Dear Helpers,

Via e-banking service, I have extracted my account data to control my
business. From the data, I use the following formula to classify the receipt
and payment types based on the transaction description.

=IF(OR((ISNUMBER(SEARCH("AAW",C2))),(ISNUMBER(SEARCH("ABT",C2)))),VALUE(RIGHT(C2,7)),IF(ISNUMBER(SEARCH("Rental",C2)),RIGHT(C2,8),IF(OR((ISNUMBER(SEARCH("ABC",C2))),(ISNUMBER(SEARCH("ABB",C2))),(ISNUMBER(SEARCH("AAC",C2))),(ISNUMBER(SEARCH("CH",C2))),(ISNUMBER(SEARCH("TAX",C2))),(ISNUMBER(SEARCH("GD",C2)))),"EXPENSE",IF(OR(ISNUMBER(SEARCH("CASH",C2)),ISNUMBER(SEARCH("-",C2))),"DEPOSIT",IF(OR((ISNUMBER(SEARCH("1234567",C2))),(ISNUMBER(SEARCH("98765432",C2))),(ISNUMBER(C2))),C2,"TRANSFER")))))

As the description changes from time to time according the bank's operation
and the "nested If" problem, I set up the above formula in one column and
another set of formula like the above to further classify the unclassified
data from the first column. I am looking for any solution to use "search"
function or vlookup a listing for identification of data based on the defined
receipt or payment types.

Thanks in advance your kind assistance.

Fanny

 

[Max]

One thought ..

For this type of OR construct:

.. IF(OR((ISNUMBER(SEARCH("ABC",C2))),(ISNUMBER(SEARCH("ABB",C2))),(ISNUMBER(SEARCH("AAC",C2))),(ISNUMBER(SEARCH("CH",C2))),(ISNUMBER(SEARCH("TAX",C2))),(ISNUMBER(SEARCH("GD",C2)))),"EXPENSE", ..

it could be replaced with something simpler like this
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2)))>0,"Expense", ..

(above uses a hardcoded listing: {..}
but its simple to add/remove elements as needed)

Or, if you create a defined col range: Expense
referring to all the elements: AAC, ABC, etc
then the above would reduce further to just:
IF(SUMPRODUCT(--ISNUMBER(SEARCH(Expense,C2)))>0,"Expense", ..

any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[T. Valko]

IF(SUMPRODUCT(--ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2)))>0,"Expense",

..

That can be reduced to:

IF(OR(ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2))),"Expense"...

 

[Fanny]

Dear All,

Thanks a million for your all kind assistance. Your suggestion saves a lot
of my time and speed the work done.

Thank you again.

Fanny

 

[Max]

Welcome, glad to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Fanny]

Dear Helpers,

I manage to use the following method to shorten the original formula rather
than the "sumproduct".

IF(OR(ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2))),"Expense"...

However, I still cannot solve the 7 "nested if" problem that I can only
classify the items with maximum 7 categories.

Pls help if there is any solution.

Thanks again.

Fanny

 

[T. Valko]

In the formula you posted you're referencing cell C5697. Are you saying that
cell C5697 will contain one the entries from the below list:

AAW 14233301
ABT 1423302
1423303
1423304 RENTAL 1
ABC 90000001
ABB 80000002
CASH Deposit
122222-56789
1234567
98765432

 

[Fanny]

Dear Valko,

I mean the data at the cell C5697 may contain the data below not one cell
having all the data below because they are transaction description extracted
from e-banking statement, i.e. each transaction done in my current account at
the bank.

Thanks for your help.

Fanny