Previous Topic Next Topic

Comparing data on 2 spredsheets

Closed
E - Sep 2, 2010 at 11:15 AM
 E - Sep 3, 2010 at 07:38 AM
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
Related:

11 responses

Answer 1 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Sep 3, 2010 at 05:48 AM
=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
Answer 2 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Sep 2, 2010 at 11:27 AM
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.
Answer 3 / 11
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Sep 2, 2010 at 07:24 PM
This could be achieved by using VLOOKUP. There are many recent examples of this in this forum.
Answer 4 / 11
E
Sep 3, 2010 at 04:21 AM
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)
Answer 5 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Sep 3, 2010 at 04:36 AM
if they found on sheet1 then what do you want to display in formula cell on sheet2 ?
Answer 6 / 11
E
Sep 3, 2010 at 04:48 AM
yes/true anything along those lines
Answer 7 / 11
E
Sep 3, 2010 at 05:42 AM
@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.
Answer 8 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Sep 3, 2010 at 05:46 AM
This Vlookup Shows You Yes & No If Data Found Or Not.........
Answer 9 / 11
E
Sep 3, 2010 at 06:05 AM
Not sure how to use that formula... thanks anyway
Answer 10 / 11
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Sep 3, 2010 at 06:13 AM
thats why i put that formula on my sample sheet which i upload.
Answer 11 / 11
E
Sep 3, 2010 at 07:38 AM
I cant download anything onto my work computer...