Duplicate rows

K

k.roberts

I have a list 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 do this within Excel, or is this easier in
Access?
 
J

Joe Mac

Try this approach, somewhat manual, but it will work...

Sort the data using the key field "School Code"
Insert a column after "School Code" and in Row 1 label it "Duplicate/Unique"
Write the following formula in Row 2 of the new "Duplicate/Unique" column-
=IF(A1=A2,"Duplicate", "Unique"), Copy this formula throughout the entire
column of "Duplicate/Unique" (Recalculate [F9] if you have this set to manual
in your Options tab)
This should identify all of the duplicate entries as they exist in the
School Code" column..
Turn on Filtering (Data-Filter-AutoFilter) for the entire spreadsheet
Select "Duplicate" under the Filtering options drop down variables for the
"Duplicate/Unique" column
Select the "Blanks" under the Filtering options drop down variables for the
"School Code" column
Delete the rows

Joe
 
K

k.roberts

That's sort of what I am looking for - I just tried it and it is
returning 'Unique' for all rows even though some are clearly duplicated
- ARGH!
 
K

k.roberts

Yeah - I sorted data first. Have gone down and checked the formula and
it is fine and should be returning the correct value.
 
Top