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
|