| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Hi,
I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
Using Edit Replace. or the LEFT feature, etc.........you can either delete
the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
Hi,
I don't want to delete the data in Table B, which is the master table. "CLR" wrote: Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
Hi Chuck - If he's adding ST to Table A items he needs to lose the leading
0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
Ok then, if you have lists in columns A and B and you wish to add "ST" to
each item in Column A, then in C1 put this formula and copy it down as far as you have data in column A..... ="ST"&A1 Then, highlight column C and do Copy PasteSpecial Values, pasting the entire column C back on itself....this will get rid of the formulas and just seave the STxxxxx. hth Vaya con Dios, Chuck, CABGx3 "JN" wrote in message ... Hi, I don't want to delete the data in Table B, which is the master table. "CLR" wrote: Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
Roger Ken, my bad, didn't see that......and my computer is messing up and
I'm not getting posts correctly and I didn't see this one of yours until after I posted my previous.....anyway, all we can do is give concepts without seeing the actual data....don't know how many of the A items have leading zeros and/or if any have two, etc etc.....of course "01A" can be replaced with "ST1A"......using Edit Replace feature..... Vaya con Dios, Chuck, CABGx3 "Ken Wright" wrote in message ... Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*------------ ---- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*------------ ---- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
:-)
"CLR" wrote in message ... Roger Ken, my bad, didn't see that......and my computer is messing up and I'm not getting posts correctly and I didn't see this one of yours until after I posted my previous.....anyway, all we can do is give concepts without seeing the actual data....don't know how many of the A items have leading zeros and/or if any have two, etc etc.....of course "01A" can be replaced with "ST1A"......using Edit Replace feature..... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/match text
If TableB was in A1:A7 and the substring you were looking for was in cell B1,
one possibility could be: =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0 If you wanted the index number of your match (assuming there's only one match): =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))),ROW(INDIRECT("1:"&R OWS(A1:A7))))0 If you have multiple matches, you can find the index of the first match (this formula must be entered with Control+Shift+Enter): =MIN(IF(--(ISNUMBER(SEARCH(D1,A1:A7,1)))=0,"",(--(ISNUMBER(SEARCH(D1,A1:A7,1)))*ROW(INDIRECT("1:"&R OWS(A1:A7)))))) "JN" wrote: I am not trying to add any characters here. I am trying to match up what in Table A with the master data in Table B. The problem is that the data in Table B contains long string of text, while table A only contains a portion of this string. For instance, in Table A, I have "1A889"; then Table B has "ST01A889000, ST01A99900... etc." I want to find if Table B has this string "1A889". Thx. "Ken Wright" wrote: Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/match text
I am not trying to add any characters here. I am trying to match up what in
Table A with the master data in Table B. The problem is that the data in Table B contains long string of text, while table A only contains a portion of this string. For instance, in Table A, I have "1A889"; then Table B has "ST01A889000, ST01A99900... etc." I want to find if Table B has this string "1A889". Thx. "Ken Wright" wrote: Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/match text
Thanks! Does the data from both tables have to be in the same format in order
for the formulas to work? I tried using the first formula =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0 on a large set of numeric data. But it doesn't seem to work. For example, Table A has "2077", "3145", "2677" in column N; Table B has "10000002077", "100000003145", "100000002677". I want to find whether "2077" from table A exists in Table B. Since the number in table B has so many digits, I am interested in the last 4 to 5 digits. If "2077" exists in table b, then I want it to show certain info in table B. I assume this part will have to be a vLookup, is this right? Thanks. "JMB" wrote: If TableB was in A1:A7 and the substring you were looking for was in cell B1, one possibility could be: =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0 If you wanted the index number of your match (assuming there's only one match): =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))),ROW(INDIRECT("1:"&R OWS(A1:A7))))0 If you have multiple matches, you can find the index of the first match (this formula must be entered with Control+Shift+Enter): =MIN(IF(--(ISNUMBER(SEARCH(D1,A1:A7,1)))=0,"",(--(ISNUMBER(SEARCH(D1,A1:A7,1)))*ROW(INDIRECT("1:"&R OWS(A1:A7)))))) "JN" wrote: I am not trying to add any characters here. I am trying to match up what in Table A with the master data in Table B. The problem is that the data in Table B contains long string of text, while table A only contains a portion of this string. For instance, in Table A, I have "1A889"; then Table B has "ST01A889000, ST01A99900... etc." I want to find if Table B has this string "1A889". Thx. "Ken Wright" wrote: Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/match text
It seems to work for me with either text or numeric data (or a combination of
the two). Are you searching an entire column of data? Sumproduct can only handle 65535 entries (most excel spreadsheets contain 65536). Are you getting an error or an improper result? The last formula I posted would give you the index number of the first match. It could be combined with the Index function to return data in a different column. "JN" wrote: Thanks! Does the data from both tables have to be in the same format in order for the formulas to work? I tried using the first formula =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0 on a large set of numeric data. But it doesn't seem to work. For example, Table A has "2077", "3145", "2677" in column N; Table B has "10000002077", "100000003145", "100000002677". I want to find whether "2077" from table A exists in Table B. Since the number in table B has so many digits, I am interested in the last 4 to 5 digits. If "2077" exists in table b, then I want it to show certain info in table B. I assume this part will have to be a vLookup, is this right? Thanks. "JMB" wrote: If TableB was in A1:A7 and the substring you were looking for was in cell B1, one possibility could be: =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0 If you wanted the index number of your match (assuming there's only one match): =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))),ROW(INDIRECT("1:"&R OWS(A1:A7))))0 If you have multiple matches, you can find the index of the first match (this formula must be entered with Control+Shift+Enter): =MIN(IF(--(ISNUMBER(SEARCH(D1,A1:A7,1)))=0,"",(--(ISNUMBER(SEARCH(D1,A1:A7,1)))*ROW(INDIRECT("1:"&R OWS(A1:A7)))))) "JN" wrote: I am not trying to add any characters here. I am trying to match up what in Table A with the master data in Table B. The problem is that the data in Table B contains long string of text, while table A only contains a portion of this string. For instance, in Table A, I have "1A889"; then Table B has "ST01A889000, ST01A99900... etc." I want to find if Table B has this string "1A889". Thx. "Ken Wright" wrote: Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up/math text
JN wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! =INDEX(ReturnRange,MATCH("*"&A2&"*",MatchRange,0)) where A2 houses the lookup value, that is, a value you want to look up. ReturnRange and MatchRange are ranges from Table B. The lookup vaue is compared with (matched against) MatchRange. ReturnRange is the range from which a corresponding value is returned. The lookup value must be a substring of some value in MatchRange for this formula to succeed. The leading 0 in 01A0000 would thwart a match against a value like ST1A0000 in Matchrange. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extra help with transpose | Excel Discussion (Misc queries) | |||
| Shade cell according to text? | Excel Discussion (Misc queries) | |||
| Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
| SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
