How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1874
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
Type Your Programming Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have two columns of addresses. I need to know what

Customer Question

Hello. I have two columns of addresses. I need to know what addresses in Column A are NOT in Column B. Can you walk me through it please? Thank you.
JA: What programs and versions are you using?
Customer: Sorry. Excel. Will check version.
JA: What is your ideal outcome? How can we solve this for you?
Customer: Excel 2016. My outcome is to have a list of addresses in Column A that are not in Column B. Thanks.
JA: Anything else you want the programmer to know before I connect you?
Customer: No.
Submitted: 22 days ago.
Category: Programming
Expert:  Jason Jones replied 22 days ago.

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.

Thank you,
Jason

Customer: replied 22 days ago.
I tried to upload it and am getting an error message.We are sorry, but the requested file doesn't exist or has expired and is no longer available.You can return to the download page and try again.Here is an example of Column A and Column B. Very straightforward. Thank you.
***** 104 LINCOLN ***** 104 WHILDIN ***** 104 YALE ***** 105 CORAL ***** 105 HARVARD
Expert:  Jason Jones replied 22 days ago.

In that case, you can upload the file directly to this site. Click the paperclip icon and upload the file.

Customer: replied 22 days ago.
there is no paperclip. I clicked on Add Files and hit SEND. Do you have it?
Expert:  Jason Jones replied 22 days ago.

Yes, I have it now. Thank you.

Expert:  Jason Jones replied 22 days ago.

There are some that partly match. How do you wish to deal with those?

For example:

In Column A there is*****/p>

In Column B there is 109 Yale

Would those be considered matching?

Customer: replied 22 days ago.
they would be considered a match. Please tell me how to do it. I need to learn this. Thank you.
Expert:  Jason Jones replied 22 days ago.

How much of the text needs to match before it would be considered a match?

Customer: replied 22 days ago.
Just the address number and street name.
Expert:  Jason Jones replied 22 days ago.

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.

- Jason

Expert:  The-PC-Guy replied 22 days ago.

so you want to know which addresses in column B are not contained within column A?

Customer: replied 22 days ago.
No. The other way. Which Addresses in Column A are NOT in Column B. Thank you.
Expert:  The-PC-Guy replied 22 days ago.

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.

Customer: replied 22 days ago.
I already told the previous expert to consider only the NUMBER and the STREET NAME. Ave, etc is not relevant.Does that help?
Expert:  The-PC-Guy replied 22 days ago.

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

Customer: replied 22 days ago.
That's fine. I can deal with that. Please describe to me how to do this. I want to learn how for future needs. Thank you.
Expert:  The-PC-Guy replied 22 days ago.

let me see if I can send back an example

Expert:  The-PC-Guy replied 22 days ago.

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.

Expert:  The-PC-Guy replied 22 days ago.

---------------------------------------------------------------------------------------------------------------

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

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

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.

Customer: replied 22 days ago.
Thanks but I need the process you used. What did you do to achieve this result? Please list the steps. Thank you.
Expert:  The-PC-Guy replied 22 days ago.

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.

Customer: replied 22 days ago.
I don't see the formulas or where you placed them. Thank you.
Expert:  The-PC-Guy replied 22 days ago.

column E and Column F starting on row 2

Customer: replied 22 days ago.
I'll look when I get on my desktop but I see no formulas on the spreadsheet. And no instructions.
Expert:  The-PC-Guy replied 22 days ago.

are you looking at the new file I sent you?

Expert:  The-PC-Guy replied 22 days ago.

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

Customer: replied 21 days ago.
There are no formulas and no instuctions. Please don't try again to upsell me on the remote session. If you have the formula and the instructions, then paste them in an email. I'm not giving good ratings until I get that. That's my bot***** *****ne. Thank you.
Expert:  The-PC-Guy replied 21 days ago.

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.

Expert:  The-PC-Guy replied 19 days ago.

did you get the updated instructions per your request?

Related Programming Questions