Duplicate records

[k.roberts]

I have a spreadsheet that contains approx 4000 rows of info - school
code, school name, address and email. Some school codes are duplicated
as a result of a workbook merge. I need to search through the
information, find the duplicated rows and delete the row that DOES NOT
contain an email address. How do I go about doing this, if I export
the information to an access database first?

 

[Roger Carlson]

If your spreadsheet is in a table format, you can just link the Excel
speadsheet into Access as a table. (Go to File > Get External Data > Link,
choose Excel as the file type) Then you can use the Find Duplicates query
wizard to find duplicate records.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Gman]

You can do this in Access but a manual Excel solution might be quicker.
(I appreciate you requested an Access solution but maybe you didn't
know about Autofilter.)

Just add an extra column to you worksheet that counts the occurrences of
the SchoolCode e.g. (assuming SChoolCode is in column A)

place this in row 2 and copy down:
=COUNTIF(A:A,A2)

Then use autofilter (Data \ Filter \ Autofilter) to filter for

"count column > 2"
and
"email column = empty"

and delete the displayed rows.

 

[k.roberts]

Thanks Gman - that is so simple and quick. Cheers!

 

[Gman]

I'm glad it worked for you!
Tara,
Gman

 

[Gman]

Oh. And I'm sure you spotted:

"count column > 2"

should have read

"count column > 1"

or >=2 I guess