Comparing data on 2 spredsheets [Closed]

Report
-
 E -
Hello,

I'm using Excel 2007. I have 2 spreadsheets. 1 of them has a list of about 9500 post codes(spreadsheet 1 for future reference), and the 2nd spreadsheet has about 12000 post codes(spreadsheet 2 for future reference).

What i'm trying to do is take the post codes from spreadsheet 2 and find out if they're in spreadsheet 1, and in column c put if they are/aren't on the 1st spreadsheet. I've searched for over 1000 post codes 1 by 1 so far, by taking post codes from spreadsheet 2 and searching for them/finding them on spreadsheet 1(ctrl f). This has taken me hours to do, and there must be a faster way to do it. However, nor me or my colleagues know of a formula on how to make this process quicker.

If anyone could put this is basic terms for me and tell me step by step how to do this, I would dearly appreciate it!

-E

11 replies

Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,0))=TRUE,"No","Yes")

Well Here Is Your Formula. If You Can Understand & Manage This Formula On Your Sheet Then Thats Good.

Else Check My Uploaded File Whenever You You Want To Download.......

Regards,
Naeem
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting 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.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
This could be achieved by using VLOOKUP. There are many recent examples of this in this forum.
Example, if it helps:

Spreadsheet 1:

Postcode 1
Postcode 2
Postcode 4

Spreadsheet 2:

Postcode 1 - yes
Postcode 2 - yes
Postcode 3 - no
Postcode 4 - yes
Postcode 5 - no

I'd like this done for all 12000 postcodes, without doing it 1 by 1. At the moment, I have to "find" - ctrl f - each postcode from spreadsheet 2, 1 by 1 on spreadsheet 1. Then I have to put manually if it is or isn't on spreadsheet 1 (yes/no)
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
if they found on sheet1 then what do you want to display in formula cell on sheet2 ?
yes/true anything along those lines
@Game Start Now - I have VLOOKUP on excel already, and I didn't really want to download anything as i'm on a work computer.
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
This Vlookup Shows You Yes & No If Data Found Or Not.........
Not sure how to use that formula... thanks anyway
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
thats why i put that formula on my sample sheet which i upload.
I cant download anything onto my work computer...

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!