Query - comparing columns from two worksheets

OscarK · Mar 12th, 2002, 05:56 PM

I use excel to hold mailing info for our community's newsletter (Sheet1). There are several empty apartments & houses I don't want to create a mailing label for. I have acquired a list of vacant apartments (SHEET2) I would like run a query comparing data then writing to SHEET3 if the data isn't found on sheet2.

Paul-Johnson · Mar 12th, 2002, 06:20 PM

Depending on the size of you list you could use a vlookup.

Or, you could use microsoft query with an outer join that would return on the items in list onethat have a match in list 2.

OscarK · Mar 13th, 2002, 12:06 AM

My list is about 1000 records, I would like to exclude records if I find a match on the other worksheet.

Dave Hawley · Mar 13th, 2002, 12:17 AM

Hi Oscar

Try this on sheet 3 starting from A2

=IF(ISNA(MATCH(Sheet2!A2,Sheet1!$A$1:$A$100,0)),Sheet1!A2,"")

This will pull in the names on sheet 1 that ARE NOT on sheet 2

After running this do a Copy Paste special-Values over the top of itself then just sort to remove blanks.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-12 23:18 ]

OscarK · Mar 14th, 2002, 01:39 PM

Thanks for the reply, I'm not sure
1463 NE Hawthorne Street1464 NE Katsura Street
1464 NE Katsura Street 1467 NE Hawthorne Street
1466 NE Katsura Street 1480 NE Katsura Street
1467 NE Hawthorne Street1491 NE Hawthorne Street
1468 NE Katsura Street 1500 NE Katsura Street
1470 NE Katsura Street 1501 NE Highmoor Court
1471 NE Hawthorne Street1505 16th Lane NE #101
1472 NE Katsura Street 1505 16th Lane NE #102
1474 NE Katsura Street 1505 16th Lane NE #202

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 05:56 PM	#1
OscarK New Member Join Date: Mar 2002 Posts: 3	I use excel to hold mailing info for our community's newsletter (Sheet1). There are several empty apartments & houses I don't want to create a mailing label for. I have acquired a list of vacant apartments (SHEET2) I would like run a query comparing data then writing to SHEET3 if the data isn't found on sheet2.

Mar 12th, 2002, 06:20 PM	#2
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	Depending on the size of you list you could use a vlookup. Or, you could use microsoft query with an outer join that would return on the items in list onethat have a match in list 2.

Mar 13th, 2002, 12:06 AM	#3
OscarK New Member Join Date: Mar 2002 Posts: 3	My list is about 1000 records, I would like to exclude records if I find a match on the other worksheet.

Mar 13th, 2002, 12:17 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Oscar Try this on sheet 3 starting from A2 =IF(ISNA(MATCH(Sheet2!A2,Sheet1!$A$1:$A$100,0)),Sheet1!A2,"") This will pull in the names on sheet 1 that ARE NOT on sheet 2 After running this do a Copy Paste special-Values over the top of itself then just sort to remove blanks. _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-12 23:18 ]

Mar 14th, 2002, 01:39 PM	#5
OscarK New Member Join Date: Mar 2002 Posts: 3	Thanks for the reply, I'm not sure 1463 NE Hawthorne Street1464 NE Katsura Street 1464 NE Katsura Street 1467 NE Hawthorne Street 1466 NE Katsura Street 1480 NE Katsura Street 1467 NE Hawthorne Street1491 NE Hawthorne Street 1468 NE Katsura Street 1500 NE Katsura Street 1470 NE Katsura Street 1501 NE Highmoor Court 1471 NE Hawthorne Street1505 16th Lane NE #101 1472 NE Katsura Street 1505 16th Lane NE #102 1474 NE Katsura Street 1505 16th Lane NE #202