Excel Advanced Filter Problem

Microsoft Excel 2003 (full product)
July 14, 2010 at 18:20:37
Specs: Windows XP
I have four columns of data, with headers on the first row and thousands of rows of data for each column. Every day I import a .csv file into a second worksheet, then copy and paste new data into my database. The .csv file that I import every day has old data that I already included in the database plus the new data I receive every day. I am trying to create a filter on the second worksheet that looks at the imported data, filters out the old data by comparing with the database, and then insert the new data into the database. Ultimately I want to create a macro for this but I am just trying to create the filter first. I've spent some hours playing with the advanced filter and no luck yet. Any suggestions or links to previous posts are appreciated. Thank you!

See More: Excel Advanced Filter Problem

Report •

July 15, 2010 at 08:04:50

I found it easier to just go with a macro.

As you haven't said where any of your data is, this macro uses the following - and if you wish to use it, you will need to change the macro to match.

Existing data (the database) is on a worksheet named "D_base"
The Imported data is placed on worksheet "Sheet2"
The unique data used to compare the new csv data with the existing database data is in Column A of both worksheets.
Row 1 of both worksheets is a header row.

On "Sheet2", create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Update' or something suitable.
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngDBStart As Range
Dim rngDBEnd As Range
Dim rngCell As Range
Dim intDBOffst As Integer

'set start of new source data (in column A - excludes header row(s))
Set rngSrcStart = Worksheets("Sheet2").Range("A2")
'find end of new source data (in column A)
Set rngSrcEnd = Worksheets("Sheet2") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set start of database data (in column A - excludes header row(s))
Set rngDBStart = Worksheets("D_base").Range("A2")
'find end of database data (in column A)
Set rngDBEnd = Worksheets("D_base") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'set first Dbase row offset counter (to paste to empty rows)
intDBOffst = 1

'loop through new source data
For Each rngCell In Range(rngSrcStart.Address, rngSrcEnd.Address)
    'test if field exists in D_base column A
    If Worksheets("D_base").Range(rngDBStart, rngDBEnd).Find(rngCell.Text, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=True) _
                Is Nothing Then
        'no match for this, so copy the row to next empty row
        rngCell.EntireRow.Copy Destination:=rngDBEnd.Offset(intDBOffst, 0)
        'increment Dbase row offset
        intDBOffst = intDBOffst + 1
    End If
Next rngCell
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Update' button to run the macro


Report •

July 15, 2010 at 09:10:33
Thank you Humar,

What do I modify in the code so I can get around the error that the copy area and the paste area are not the same size? My database worksheet has 8 columns (8 headers) but I my imported data worksheet only has the first 4 columns. The last 4 columns I have to manually put in data and that is unavoidable. Also, does the new data get inserted into the top rows?


Report •

July 15, 2010 at 09:41:16

You can copy just four columns on each row by replacing the EntireRow statement with a Resize(rows,columns) statement:

        'no match for this, so copy 1st four columns to next empty row
        rngCell.Resize(1, 4).Copy Destination:=rngDBEnd.Offset(intDBOffst, 0)

The macro adds new data to the end of the existing data.

To insert it at say row 2 each time requires some changes;
A full new row must be inserted, then the four cells copied, then the database range has to be changed, so that the Find function still searches to the (new) last row.

In the present setup, new rows are new data and don't need to be searched.

If the existing macro, changed for the four column copy, works, then let me know and I will see if I can change the macro to insert new data at the top.

If there is a header row or rows, please let me know.


Report •

Related Solutions

July 15, 2010 at 09:54:12
Thank you for the prompt response,

The existing macro works and it copies the new data into the bottom of the database. However, my unique data is column C so it only copies the C and D columns.

The top row for the database worksheet and imported data worksheet is the header row.


Report •

July 15, 2010 at 09:58:51
Also, it might help to know that column A is a date for each entry. The database worksheet is sorted by date so it could be easier to just add the new data in the bottom and sort it in the macro.

Report •

July 15, 2010 at 10:47:56

Here is the revised code:
rngCell.Offset(0, -2).Resize(1, 4).Copy Destination:=rngDBEnd.Offset(intDBOffst, -2)

This starts the 4 column selection 2 columns to the left of column C
The destination is also offset 2 columns to the left.


Report •

July 16, 2010 at 06:52:05

Here is code for a sort that you could add to the end of the existing code.

'sort data on DB sheet using column A
'include cells in all 8 columns A to H in the sort
 With Worksheets("D_Base")
 .Columns("A:H").Sort Key1:=.Range("A1"), _
        Order1:=xlAscending, _
        Header:=xlYes, _
        Orientation:=xlTopToBottom, _
End With


Report •

July 16, 2010 at 14:42:35
Thank you Humar for your help. This has been very helpful in learning and understanding Excel.

Report •

July 16, 2010 at 15:01:23
Hello picoarthur,

You're very welcome.

Best wishes to you



Report •

Ask Question