Using search function to classify data

  • Thread starter Thread starter Fanny
  • Start date Start date
F

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
 
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", ..
Click to expand...

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
---
 
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2)))>0,"Expense",
..
Click to expand...

That can be reduced to:

IF(OR(ISNUMBER(SEARCH({"AAC";"ABC";"TAX";"GD";"CH";"ABB"},C2))),"Expense"...
 
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
 
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
 
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
 
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
 
Back
Top