Previous Topic Next Topic

Match strings of characters & return content

Solved/Closed
maggs - Jun 16, 2010 at 03:15 PM
 maggs - Jun 17, 2010 at 02:02 PM
Hello,


I was able to define this formula to return the row in which the match content was found:

=MATCH("*"&MID($B2&REPT(CHAR(6),6),(ROW(OFFSET($A$1,0,0,LEN($B2),1))),6)&"*",$A:$A,0)

Is there a way to return the actual content?

Thanks!

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 16, 2010 at 06:30 PM
you can use index, or indirect functions
0
maggs
Jun 17, 2010 at 10:45 AM
the indirect function worked. THANK YOU!!!
0
maggs
Jun 17, 2010 at 11:36 AM
Is there a way to return more than one value if more than one match is found? Thanks!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 17, 2010 at 11:46 AM
Match will show the first hit (if any) You may want to create your own function to do multiple matches

What exactly are you trying to do. May be there is some other way too if only a little more is known about what is the objective..
0
maggs
Jun 17, 2010 at 01:31 PM
I am trying to locate and remove duplicates customer accounts by matching address information from a current property list against all existing customer accounts. I am searching a list of 800 properties against 30,000 accounts.

Column A: addresses for all existing accounts, 30,000 records
Column B: address nee to search, 800 records

Column A may contain more than one match for Column B.

Thanks so much for your help!!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 17, 2010 at 01:50 PM
I am very confused over what you want. If you want to see a duplication, so as soon as a match is found, we know that it is a duplicate. Are you saying on in column A (30000) there is an address that will appear more than once in column B (800)

May be if you could put up some sort of sample file and then explain the issue that you are trying to resolve. Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
0