Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using search function to classify data | Excel Discussion (Misc queries) | |||
keyboard shortcuts - classify data | Excel Discussion (Misc queries) | |||
The SEARCH function and parsing data | Excel Worksheet Functions | |||
Function to search a row of data. | Excel Worksheet Functions | |||
HOW TO USE FIND OR SEARCH FUNCTION TO FILL DATA | Excel Worksheet Functions |