Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
I am reposting this to see if I can get another answer.
I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Hi
You probably need to add the optional 4th argument to Vlookup, of either FALSE or 0. This is necessary when the data is not sorted alphabetically. C1: =VLOOKUP(A1,CATALOG,2,0) -- Regards Roger Govier "micmed" wrote in message ... I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
If I understand your posting correctly, you are saying that you have
something like this in column A: 1234 567 8914 23456 i.e. part numbers - you do not state if these are in order or if they follow a particular format (eg 6 digit). In column B you have text which contains the part number, something like: 1234 wheel 567 passenger door 8914 headlamp 23456 rear bumper Again, you give us no examples of what this is like - are the part numbers always to the left, or can they be embedded within the text? Are they always separated from the text by at least one space? If this is a fair description of your problem, then VLOOKUP won't work on its own. But, in order to give you some further help you do need to describe your data a bit more fully with examples. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Thanks for asking Pete. The part numbers do not follow any particular format.
They could be: 12345 34-5678 FL-975 3K-23G-85 WPL-1176 Some of the part numbers are embedded, some are to the left and all are seperated by at least one space. WPL-1176 €¢ $39.99 6 Ft. FWC-9-9-6 . . . . . . . . . . . . . . . . . . . . . . . . . $44.9510 Ft. FW6-FW6CM FWA-6F-4M FWA-6M-4F FW-6J-4P FW6-BLCM "Pete" wrote: If I understand your posting correctly, you are saying that you have something like this in column A: 1234 567 8914 23456 i.e. part numbers - you do not state if these are in order or if they follow a particular format (eg 6 digit). In column B you have text which contains the part number, something like: 1234 wheel 567 passenger door 8914 headlamp 23456 rear bumper Again, you give us no examples of what this is like - are the part numbers always to the left, or can they be embedded within the text? Are they always separated from the text by at least one space? If this is a fair description of your problem, then VLOOKUP won't work on its own. But, in order to give you some further help you do need to describe your data a bit more fully with examples. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
See if one of these works for you:
This one returns TRUE/FALSE: C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 This one returns the Row Number of the first matched item in CATALOG: C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\ If there is the possibility of multiple matches, let us know. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: See if one of these works for you: This one returns TRUE/FALSE: C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 This one returns the Row Number of the first matched item in CATALOG: C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I knew did and did not exist in CATALOG. Any other ideas? "Ron Coderre" wrote: Actually, my second formula returns the SUM of the row numbers if there is more than one match. :\ If there is the possibility of multiple matches, let us know. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: See if one of these works for you: This one returns TRUE/FALSE: C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 This one returns the Row Number of the first matched item in CATALOG: C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
I just tested my formulas with your posted data formats.
Just to reiterate: You have a part number list in Col_A. You have a catalog list in Col_B of the same sheet. You want to know if each part number in Col_A exists on any line in the catalog. If that Is that correct, then both formulas in C1 and copied down worked fine. I suspect that something else is impacting your results. Perhaps the references need to be adjusted. *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: Thanks Ron and Roger for responding. Rogers solution still produces #N/A and Ron's returns FALSE for all rows in column A. I tried both on cells that I knew did and did not exist in CATALOG. Any other ideas? "Ron Coderre" wrote: Actually, my second formula returns the SUM of the row numbers if there is more than one match. :\ If there is the possibility of multiple matches, let us know. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: See if one of these works for you: This one returns TRUE/FALSE: C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 This one returns the Row Number of the first matched item in CATALOG: C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Does it matter that some of the cells in Col_A have a warning stating the
number is stored as text? I have tried changing all of Col_A to General but that doasn't get rid of the warning. "Ron Coderre" wrote: I just tested my formulas with your posted data formats. Just to reiterate: You have a part number list in Col_A. You have a catalog list in Col_B of the same sheet. You want to know if each part number in Col_A exists on any line in the catalog. If that Is that correct, then both formulas in C1 and copied down worked fine. I suspect that something else is impacting your results. Perhaps the references need to be adjusted. *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: Thanks Ron and Roger for responding. Rogers solution still produces #N/A and Ron's returns FALSE for all rows in column A. I tried both on cells that I knew did and did not exist in CATALOG. Any other ideas? "Ron Coderre" wrote: Actually, my second formula returns the SUM of the row numbers if there is more than one match. :\ If there is the possibility of multiple matches, let us know. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: See if one of these works for you: This one returns TRUE/FALSE: C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 This one returns the Row Number of the first matched item in CATALOG: C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "micmed" wrote: I am reposting this to see if I can get another answer. I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B. This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2) All I get is #N/A - I know that the value in A1 exists in CATALOG CATALOG=The name of array B1:B19989 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
I think the solution is in a minor modification of Ron's formula. The
suggested formula =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 returns TRUE only if the code in A1 appears exactly once in CATALOG and I suspect this is not the case. I suggest you modify to: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0 HTH Kostis Vezerides |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in one table from another.
Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I want it only to search for 602 and not $602.35 or CR602-12 etc. "vezerid" wrote: I think the solution is in a minor modification of Ron's formula. The suggested formula =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1 returns TRUE only if the code in A1 appears exactly once in CATALOG and I suspect this is not the case. I suggest you modify to: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0 HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
1-variable Data Table dependent on MS Query fails to update correctly | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
changing proportion of chart and data table | Charts and Charting in Excel |