Need Help Comparing Two Lists in Excel

August 29, 2011 at 13:38:04
Specs: Windows XP
I am trying to match up a list of physical location of a property to their ship to address and I cant figure how to do it..

For the ship to address

I have something like this in column P

260-261 HENRY LLC Stephen Henry 123 Main Street Cleveland, OH 44102

In Column R, I have

OH

How can I see that the information in Column R matches the state in the address..



See More: Need Help Comparing Two Lists in Excel

Report •


#1
August 30, 2011 at 07:47:24
very possible and quite easy, however it would be better if you provide a spreadsheet with some dummy data so we can see exactly what the format of your spreadsheet is.

Question,

does the entire address of only have one comma? the address only seems to have a "," after Cleveland is that the format of the address in your spreadsheet?


Report •

#2
August 30, 2011 at 11:39:19
How would you suggest that he "provide a spreadsheet"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
August 30, 2011 at 13:22:24
With your Address in cell P1
and your two character State Code in cell R1

Find an empty cell and try this:

=IF(R1=0," ",IF(ISERROR(FIND(LOWER(R1),RIGHT(LOWER(P1),8),1)),"No","Yes"))

CAVEAT:
your address must end with a 5 digit zip
and a 2 character State Code,
and the above should work.

Make life easier on yourself and break up the address.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
August 31, 2011 at 01:12:01
Hi DerbyDad03 as an attachment? possibly im getting confused with another forum i am a member of, users can add attachments to their post. Otherwise maybe you can suggest something :)

Report •

#5
August 31, 2011 at 04:52:55
As long as we're making assumptions about the data, I'll assume that the last 8 characters of the string are a 2 character state, a space and 5 digit zip.

e.g. OH 44102

Then I would use this:

=IF(MID(P1,LEN(P1)-7, 2)=R1,"Yes","No")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 31, 2011 at 04:56:27
AlwaysWilling:

Attachments are not allowed in this forum.

2 options that come to mind are:

1 - Post the file on a file sharing website and post the link in this forum.
2 - Click on the blue line line at the bottom of any of my posts and follow the instructions on how to post data in this forum so that the columns line up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question