MS Excel 2003 - Anyone know how to delete rows that contain duplicate data?

Question

I have an excel spreadsheet that has about 18k rows and three columns. I want to use this as a key in an Access database. So the three columns are

[IDKeyNumber]    [Name]    [Category]

The problem is in certain places the IDnumber is duplicated because the category has two category codes (typeA & typeB)

All I really need to know how to do is delete every "row/record" in the excel spreadsheet WHERE the ID#s & Name matches (as in matches another record to isolate the duplication) AND the Category = TypeB.

So I just want to get rid of all the duplicate rows/records that have this TYPEB in category field.

thanks!

IA: some rows or records have a valid TypeB category that is not a duplication, that is why i need the record = record except for category idea...thanks

Answer 1

Let's say the columns are A, B, and C.

1. add column headers (id, name, category) above the columns
2. Sort by A & B. (if you need to preserve the sort order, see below)
3. in D2, put the formula "=AND(A2 = A1, B2 = B1, C2 = "TypeB")"
4. fill down
5. Use AutoFilter to select only the rows where D is false.
6. copy and paste as needed.

For preserving the sort order, you'll need another column - let's say E because we used D for the formula. Before sorting, put 1 into E2, 2 into E3, and fill in the series. You can do this by dragging the grow handle, or putting the values actually into D2 and D3 so they're adjacent to filled cells, then double-click the grow handle. There's also a command somewhere on the menus to do this, but I don't recall its name or location.

Answer 2

see this it will work http://stackoverflow.com/questions/1175281/sql-query-delete-duplicates-if-more-than-3-dups

Answer 3

@Carls suggestion or something similar is the quickest way to get what you need in excel.

Another option may be to just set up the table in Access and set all the primary keys. Then just import the data and ignore any errors that come out of it.

Answer 4

It is possible to use ADO and SQL with Excel, for example:

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT id, name, max(type) AS type FROM [Sheet1$] GROUP BY id, name"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet2").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs