CODEWITHSUNDEEP
excelvbaduplicatesspreadsheet
Justin
Justin

Reputation: 4539

Delete rows from an Excel spreadsheet that contain duplicate data?

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

Upvotes: 2

Views: 1667

Answers (3)

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 4

Mark Nold
Mark Nold

Reputation: 5698

@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.

Upvotes: 1

Carl Manaster
Carl Manaster

Reputation: 40336

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.

Upvotes: 3

Related Questions