Look up/math text

[JN]

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!

[CLR]

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!
>
>

[JN]

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!
> >
> >

[Ken Wright]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>>
>>

[CLR]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
> > >
> > >

[CLR]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >>
> >>
>
>

[Ken Wright]

:-)

"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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.....
>

[JMB]

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:"&ROWS(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:"&ROWS(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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >>
> > >>
> >
> >
> >

[JN]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >>
> >>
>
>
>

[JN]

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:"&ROWS(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:"&ROWS(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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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!
> > > >>
> > > >>
> > >
> > >
> > >

[JMB]

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:"&ROWS(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:"&ROWS(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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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!
> > > > >>
> > > > >>
> > > >
> > > >
> > > >

[Aladin Akyurek]

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.