| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up part of a number within a serial number and cpy back assoc
I have a spreadsheet with 2 tabs.
First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy backassoc
Put this in L1 of your parts sheet:
=INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58*pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory * * A * * * * * * * * * * H 123 * * * * * * ABBC.1234467 456 * * * * * * ABBC.1234547_RM 897 * * * * * * ABBC.1234564_FT Sheet parts * * E * * * * * * * * * * L 1234547 * * * * * 456 1234564 * * * * * 897 1234467 * * * * * 123 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back assoc
Try this
=VLOOKUP(--MID(H1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H1&"012345 6789")),SUMPRODUCT(LEN(H1)-LEN(SUBSTITUTE(H1,{0,1,2,3,4,5,6,7,8,9},"")))),Par ts!E1:L36,8,FALSE) Mike "Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back assoc
Hi,
you can extract the middle part in a formula with =MID(A1,6,7) if all the enteries are consistant. This will return a text entry, if you need it as a number then =--MID(A1,6,7) Then use VLOOKUP against this column -- Thanks, Shane Devenshire "Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
Mike, does it mater if my serial numer are random? I put 123456 as an example
put they can be 1245978 or 6853247 etc.. any combination. "Mike H" wrote: Try this =VLOOKUP(--MID(H1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H1&"012345 6789")),SUMPRODUCT(LEN(H1)-LEN(SUBSTITUTE(H1,{0,1,2,3,4,5,6,7,8,9},"")))),Par ts!E1:L36,8,FALSE) Mike "Seantastic" wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
I get an error
"Pete_UK" wrote: Put this in L1 of your parts sheet: =INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
Pete can explain what each part of your formula does? I'm a novice to this
and when a copied and pasted it into the L1 cell it says " The formula you type contains an error" and it highlights A,MATCH. I am using excel 2003 - does that change anything? "Pete_UK" wrote: Put this in L1 of your parts sheet: =INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
I didn't know how many entries you had in the inventory sheet, so I
used full column references. The MATCH part of the formula is using wildcards either side of E1 to find an exact match in column H of the inventory sheet. If it finds one then it returns the row number where the match occurs. This is then used in the INDEX part of the formula to return the contents from column A on the same row as where the match occurred. The formula worked fine in my test set up (just on one sheet, though), so perhaps you didn't copy all of it - did you get both brackets from the end? Pete On Oct 31, 9:26*pm, Seantastic wrote: Pete can explain what each part of your formula does? I'm a novice to this and when a copied and pasted it into the L1 cell it says " The formula you type contains an error" and it highlights A,MATCH. I am using excel 2003 - does that change anything? "Pete_UK" wrote: Put this in L1 of your parts sheet: =INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory * * A * * * * * * * * * * H 123 * * * * * * ABBC.1234467 456 * * * * * * ABBC.1234547_RM 897 * * * * * * ABBC.1234564_FT Sheet parts * * E * * * * * * * * * * L 1234547 * * * * * 456 1234564 * * * * * 897 1234467 * * * * * 123- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
Actually, another thing it might be is if you use a semicolon rather
than comma to separate parameters in functions. This will depend on your regional settings. If so, just use ; instead of , Pete On Nov 1, 2:28*am, Pete_UK wrote: I didn't know how many entries you had in the inventory sheet, so I used full column references. The MATCH part of the formula is using wildcards either side of E1 to find an exact match in column H of the inventory sheet. If it finds one then it returns the row number where the match occurs. This is then used in the INDEX part of the formula to return the contents from column A on the same row as where the match occurred. The formula worked fine in my test set up (just on one sheet, though), so perhaps you didn't copy all of it - did you get both brackets from the end? Pete On Oct 31, 9:26*pm, Seantastic wrote: Pete can explain what each part of your formula does? I'm a novice to this and when a copied and pasted it into the L1 cell it says " The formula you type contains an error" and it highlights A,MATCH. I am using excel 2003 - does that change anything? "Pete_UK" wrote: Put this in L1 of your parts sheet: =INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory * * A * * * * * * * * * * H 123 * * * * * * ABBC.1234467 456 * * * * * * ABBC.1234547_RM 897 * * * * * * ABBC.1234564_FT Sheet parts * * E * * * * * * * * * * L 1234547 * * * * * 456 1234564 * * * * * 897 1234467 * * * * * 123- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up part of a number within a serial number and cpy back a
Thanks Pete! So far you are 2 for 2 with the best solutions.
Before I got your answer I re-use the solution you gave me for another one. I copy the serial num column into A and used "replace" in that column to remove the "ABBC.", the "_RM" and the "_FT" which left me just the middle numbers. I then used your VLOOK solution from my last problems and it worke great!! Thank you so much for your help!!! "Pete_UK" wrote: Actually, another thing it might be is if you use a semicolon rather than comma to separate parameters in functions. This will depend on your regional settings. If so, just use ; instead of , Pete On Nov 1, 2:28 am, Pete_UK wrote: I didn't know how many entries you had in the inventory sheet, so I used full column references. The MATCH part of the formula is using wildcards either side of E1 to find an exact match in column H of the inventory sheet. If it finds one then it returns the row number where the match occurs. This is then used in the INDEX part of the formula to return the contents from column A on the same row as where the match occurred. The formula worked fine in my test set up (just on one sheet, though), so perhaps you didn't copy all of it - did you get both brackets from the end? Pete On Oct 31, 9:26 pm, Seantastic wrote: Pete can explain what each part of your formula does? I'm a novice to this and when a copied and pasted it into the L1 cell it says " The formula you type contains an error" and it highlights A,MATCH. I am using excel 2003 - does that change anything? "Pete_UK" wrote: Put this in L1 of your parts sheet: =INDEX(inventory!A:A,MATCH("*"&E1&"*",inventory!H: H,0)) and copy down as required. Hope this helps. Pete On Oct 31, 4:58 pm, Seantastic wrote: I have a spreadsheet with 2 tabs. First tab is named inventory In column H the are serial numbers in the following formats ABBC.1234567 ABBC.1234567_RM ABBC.1234567_FT The only part of the serial number that interests me is the middle numbers (i.e. 1234567) In column A I have a list of part code In my second tab called parts In column E there is a list of the middle numbers found in the first tab (i.e. 1234567) In my I would like to look up the number in Column E from sheet parts (my second tab) that is found somewhere in column H in the middle of the serial number in sheet inventory, get the associated part code from column A and copy it back into sheet parts (my second tab) in column L Sheet inventory A H 123 ABBC.1234467 456 ABBC.1234547_RM 897 ABBC.1234564_FT Sheet parts E L 1234547 456 1234564 897 1234467 123- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| automatic Serial number | Excel Discussion (Misc queries) | |||
| increase serial number | Excel Worksheet Functions | |||
| Serial number generate | Excel Worksheet Functions | |||
| form serial number | Excel Discussion (Misc queries) | |||
| generate serial number | Excel Worksheet Functions | |||
Linear Mode
