Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find cell entry contained anywhere within a text string
A colleague has asked for help producing a formula and it has me stumped.
What she has is two files, one contains a list of devices where column A contains the TAG Number of the Device, and Column B contains a Description of the Device. The Second File is a 'Cabling Schedule' where column D (may or may not) contain the Description of a Device, and could contain more information about it than the Device File. Column E of this File contains the number of the Cable Schedule/ Drawing, that my colleague wants returned to the Device File once a match is found. EG: (sample) Device File (A) 123456 (B) Solenoid Valve (A) 234567 (B) Solenoid Valve EG (sample) Cable Schedule (D) Conveyor P207 Solenoid Valve 123456 Junction Box (E) 060-E-12345 (D) Conveyor P207 Local Control Station Solenoid Valve 123456 (E) 060-E-12345 (D) Motor Control Centre 243567 (E) 061-E-2456 Thing is, there is no consistancy to the character length of the TAG number, nor is there any specific location where the TAG number may be contained in the text of Col (D) in the second of the two files, and once it is located, the contents of Column E are to be placed in Column C of the Device File. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find cell entry contained anywhere within a text string
Assume you have this reference data in D2:E2 down in a sheetnamed: x
Conveyor P207 Solenoid Valve 123456 Junc 060-E-12346 Conveyor P207 Local Solenoid Valve 234567 060-E-12349 etc [Copy the actual ref sheet over to the same file, rename it as: x Makes the expression much shorter/simpler. You can always rename the source sheet back to whatever after you get the expression up n running, and leave it to Excel to auto-change the sheetname] Then in another sheet, Assume you have this data in A2:B2 down 123456 Solenoid Valve 234567 Solenoid Valve etc where the fuzzy search string is a concat of cols B & A, eg: Solenoid Valve 123456 Solenoid Valve 234567 Place in C2, normal ENTER: =IF(OR(A2="",B2=""),"",INDEX(x!E$2:E$10,MATCH(TRUE ,INDEX(ISNUMBER(SEARCH(TRIM(B2&" "&A2),x!D$2:D$10)),),0))) Copy C2 down to extract the desired results from x's col E based on a fuzzy search of the concat string through x's col D. Adapt the ranges to suit the actual extents of the source data in x. Do high-five this response, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "VickiMc" wrote: A colleague has asked for help producing a formula and it has me stumped. What she has is two files, one contains a list of devices where column A contains the TAG Number of the Device, and Column B contains a Description of the Device. The Second File is a 'Cabling Schedule' where column D (may or may not) contain the Description of a Device, and could contain more information about it than the Device File. Column E of this File contains the number of the Cable Schedule/ Drawing, that my colleague wants returned to the Device File once a match is found. EG: (sample) Device File (A) 123456 (B) Solenoid Valve (A) 234567 (B) Solenoid Valve EG (sample) Cable Schedule (D) Conveyor P207 Solenoid Valve 123456 Junction Box (E) 060-E-12345 (D) Conveyor P207 Local Control Station Solenoid Valve 123456 (E) 060-E-12345 (D) Motor Control Centre 243567 (E) 061-E-2456 Thing is, there is no consistancy to the character length of the TAG number, nor is there any specific location where the TAG number may be contained in the text of Col (D) in the second of the two files, and once it is located, the contents of Column E are to be placed in Column C of the Device File. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find cell entry contained anywhere within a text string
Ok, you have to find both the tag number and the device?
Let's assume the setup is such: Sheet1 A2 = 123456 B2 = Solenoid Valve Sheet2 D2:E4 = Conveyor P207 Solenoid Valve 123456 Junction Box...060-E-12345 Conveyor P207 Local Control Station Solenoid Valve 123456...060-E-12345 Motor Control Centre 243567...061-E-2456 Enter this array formula** on Sheet1 C2: =INDEX(Sheet2!E2:E4,MATCH(1,(ISNUMBER(SEARCH(A2,Sh eet2!D2:D4)))*(ISNUMBER(SEARCH(B2,Sheet2!D2:D4))), 0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "VickiMc" wrote in message ... A colleague has asked for help producing a formula and it has me stumped. What she has is two files, one contains a list of devices where column A contains the TAG Number of the Device, and Column B contains a Description of the Device. The Second File is a 'Cabling Schedule' where column D (may or may not) contain the Description of a Device, and could contain more information about it than the Device File. Column E of this File contains the number of the Cable Schedule/ Drawing, that my colleague wants returned to the Device File once a match is found. EG: (sample) Device File (A) 123456 (B) Solenoid Valve (A) 234567 (B) Solenoid Valve EG (sample) Cable Schedule (D) Conveyor P207 Solenoid Valve 123456 Junction Box (E) 060-E-12345 (D) Conveyor P207 Local Control Station Solenoid Valve 123456 (E) 060-E-12345 (D) Motor Control Centre 243567 (E) 061-E-2456 Thing is, there is no consistancy to the character length of the TAG number, nor is there any specific location where the TAG number may be contained in the text of Col (D) in the second of the two files, and once it is located, the contents of Column E are to be placed in Column C of the Device File. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif text is contained winthin a longer text string in a cell | Excel Worksheet Functions | |||
find partial text contained in another cell | Excel Discussion (Misc queries) | |||
Find & Replace a string contained in a link | Excel Discussion (Misc queries) | |||
Countif function for instances of text string contained | Excel Worksheet Functions | |||
Countif function for instances of text string contained | Excel Worksheet Functions |