Hello,I am the person that will be helping you today. May I take a look at the document or an example of it?
Please, be forewarned that the site is not secure:- Click the following link: http://www.wikisend.com- Upload the file to that website- Once it is uploaded, the resulting page will display a "File ID" number.- Please, give me that "File ID Number" Please, send your reply below in the box. After you type your message, click the Send button.
In that case, you can upload the file directly to this site. Click the paperclip icon and upload the file.
Yes, I have it now. Thank you.
There are some that partly match. How do you wish to deal with those?
In Column A there is*****/p>
In Column B there is 109 Yale
Would those be considered matching?
How much of the text needs to match before it would be considered a match?
I have run out of ideas.
For this reason, I am opting out of the question and allowing another expert the chance to help.
The next expert will see all that is on this page, so there will be no need to repeat anything.
When another expert picks this up, you will be notified by email.
Thank you for your patience.
so you want to know which addresses in column B are not contained within column A?
that is not possible because of the length, you cannot search a longer value in a shorter value
unless we cut out the word ave, or dr, or rd, st, or whatever, as well as any punctuation.
Also there are some cells in both columns that contain multiple numbers or puntuation or streets in the same cell. We cannot use those to get any reliable results.
IF you separated it all out making sure only 1 street / road per cell in each column, it can probably be done.
yes, I can eliminate ave, rd from column A with a formula, however there are some examples where it has multiple numbers in the cell I cannot get an accurate result in those cases.
cell, a3, b4, or b13 for example
let me see if I can send back an example
ok so this file contains 2 columnns E and F
the first column takes the value in column A and deletes the last word following the last space, that being AVE, ST, DR, etc.. it is assumed that the last word in every cell in column A will be one of those values, in the case where you have multiple values in a single cell it will not return the results you want.
the 2nd column determines if that value or part of that value from column A is contained in any of the cells in column B, if it is not it displays a message, you can change that message by changing what is in the quotes in the formula.
Note that you may notice some false positives or some values that are not in the columns that should be, this is because any cell in either column that contains multiple values may cause strange results.
let me know if you have any questions, problems, or concerns
PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME
IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION
TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN
Do not rate negatively, instead continue the conversation with me so I can address any of your concerns
if you have any questions in the future you can visit my profile
and ask a question right in my box,
also you can put "PC Guy only" in your question title if you want to get to me.
there weren't really any steps. just 2 formulas as I explained above.
The first formula removes the last word, the 2nd determines if it is in the other column or not.
column E and Column F starting on row 2
are you looking at the new file I sent you?
shal we set up a remote session.
I am not sure why you don't see the formulas, I just double checked the file I sent and they were therer
i don't understand I just checked the file again and the formulas are there. I was only trying to help you because I thought it was something on your computer.
this is formula 1
=TRIM(LEFT(A2,FIND("@",SUBSTITUTE(TRIM(A2)," ","@",(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))/LEN(" ")))))
based on your example sheet, paste this in cell E2.
here is formula 2
=IF(ISNA(VLOOKUP(E2&"*",B:B,1,0)),"Not in B","")
based on your sample file paste this in cell F2
then once those 2 formulas are pasted, you can copy and paste them to all of the other cells in the E and F columns that you need to do this
select cell E2, and copy
then hilight all of the cells in the E column that you want to paste the formula into and paste
do the same thing for the F column.
did you get the updated instructions per your request?