Using search function to classify data
 

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(SEAR CH("ABT",C2)))),VALUE(RIGHT(C2,7)),IF(ISNUMBER(SEA RCH("Rental",C2)),RIGHT(C2,8),IF(OR((ISNUMBER(SEAR CH("ABC",C2))),(ISNUMBER(SEARCH("ABB",C2))),(ISNUM BER(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

Using search function to classify data
 

One thought ..

For this type of OR construct:
.. IF(OR((ISNUMBER(SEARCH("ABC",C2))),(ISNUMBER(SEARC H("ABB",C2))),(ISNUMBER(SEARCH("AAC",C2))),(ISNUMB ER(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
---
"Fanny" wrote:
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(SEAR CH("ABT",C2)))),VALUE(RIGHT(C2,7)),IF(ISNUMBER(SEA RCH("Rental",C2)),RIGHT(C2,8),IF(OR((ISNUMBER(SEAR CH("ABC",C2))),(ISNUMBER(SEARCH("ABB",C2))),(ISNUM BER(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.

Using search function to classify data
 

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"...

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
One thought ..

For this type of OR construct:
..
IF(OR((ISNUMBER(SEARCH("ABC",C2))),(ISNUMBER(SEARC H("ABB",C2))),(ISNUMBER(SEARCH("AAC",C2))),(ISNUMB ER(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
---
"Fanny" wrote:
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(SEAR CH("ABT",C2)))),VALUE(RIGHT(C2,7)),IF(ISNUMBER(SEA RCH("Rental",C2)),RIGHT(C2,8),IF(OR((ISNUMBER(SEAR CH("ABC",C2))),(ISNUMBER(SEARCH("ABB",C2))),(ISNUM BER(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.

Using search function to classify data
 

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

Using search function to classify data
 

Welcome, glad to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Fanny" wrote in message
...
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

Using search function to classify data
 

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

Using search function to classify data
 

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

--
Biff
Microsoft Excel MVP


"Fanny" wrote in message
...
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

Using search function to classify data
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Fanny" wrote in message
...
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