Filter and merging data

[Elliottj2121]

Hello I am wondering if someone could help me filter and merge two data sets. I have an ongoing workbook that I add data to every week from a different workbook. I have written some code already to copy the data from one workbook to the working workbook and format it the way I want it. However, I don't know how to further format, filter and merge the two data sets. I attached screenshots of what I am trying to do. I am looking for additional coding to have it look in column C and if two values match copy the corresponding value in columns A and E.

Sub MasterARdue45()
    Call OpenWkbWorkingArdue45
    Call Ardue45formatting
    Call CopyData
End Sub

Option Explicit
Sub OpenWkbWorkingArdue45()
Dim sPath As String

    sPath = Environ("USERPROFILE") & "\Desktop\WorkingARdue45.xlsx"
    Workbooks.Open Filename:=sPath
End Sub

Sub Ardue45formatting()

With Workbooks("Ardue45.xls").Worksheets(1)
    Range("A2:A500").Select
    Selection.ClearContents
    Columns("I:I").ColumnWidth = 16.14
    Columns("C:C").ColumnWidth = 12.29
    Columns("C:C").ColumnWidth = 15.71
    Columns("C:C").ColumnWidth = 18.29
    Columns("B:B").ColumnWidth = 15.86
    Columns("A:I").Select
    Selection.AutoFilter
    Range("D13").Select
End With
    
With ActiveSheet
    For Each cell In .Range("A1:" & .Range("A1").End(xlDown).Address)
        If .Cells(cell.Row, 7).Value > 0 Then
            cell.EntireRow.Font.Bold = True
        End If
    Next
End With

End Sub

Sub CopyData()

Dim wbCopy As Worksheet
    Dim wbDest As Worksheet
    Dim lr As Long
    Dim lrTarget As Long
    Set wbCopy = Workbooks("Ardue45.xlsx").Worksheets(1)
    Set wbDest = Workbooks("WorkingARdue45.xlsx").Worksheets(1)

    
    wbCopy.Activate
    Sheets(1).Select
    lr = wbCopy.Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Range("A2:I" & lr).Copy
    
    wbDest.Activate
    Sheets(1).Select
    lrTarget = wbDest.Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Cells(lrTarget + 2, 1).Select
    ActiveSheet.Paste
    Cells(1, 1).Select

End Sub

[/CODE]
[/CODE]




The first image below is how my data looks with the code above. The first two rows are old data and the remaining rows are the new data. The second image is how I would like it to look. Basically copying any data in columns A and E from the old data set and paste it into the new data set if there is a duplicate value in column C.

 

[vw412]

I am looking this over and have some questions.

You have two workbooks; 1 is WorkingARDue45 and the other is ARDur45. Is that correct.?
You are moving data from WorkingARDue45 to ARDue45. Is that correct?
The code you have posted seems to erase data from ARDue45, set column widths of the columns, setup autofilters, then copy data wholesale from WorkingARDue45 to ARDue45. Is that correct?

If all this is correct then what is needed? Looking at the image of the worksheet you want as a final it seems what I described above is doing what you want.

Since you are asking about help to filter and merge data, you need to show us what you want to accomplish beyond what you are already doing.

 

[Elliottj2121]

vw412 thanks for the reply. Yes I have two workbooks. But I am moving data from Ardue45 to WorkingARdue45. The code I have written accomplishes the moving and formatting the data as shown in the top screenshot. However, I want to eliminate duplicates in a certain way.


If you look at the first screenshot, "Smith Co" has a customer number of 222222 and is listed twice. Smith Co's data in row two is old data and the data in row five is new data. I want to delete the old data row except for values in column E, in this case "Emailed Sally". The second screenshot is the goal illustrating what I am looking for.

I hope that this makes sense. Thanks again!

 

[vw412]

I think I understand. Could there ever be duplicates in ARDue45, e.g. 3 rows for customer 2222222? If so how should that be handled?

My apologies, I mis-read the ARDueformatting code. I thought you were clearing the whole sheet but actually you are just clearing column A. That is cleared up.

 

[Elliottj2121]

After the data is copied from Ardue45 to WorkingARdue45, there will never be more than two rows of the same customer number e.g. 2222222 will only appear twice. What I am doing is taking this week's new data, Ardue45, and copying it to last week's data (WorkingARdue45) below the last row.

Yes I am just clearing column A in Ardue45 before its copied over to WorkingARdue45. Ardue45 is exported data from Showcase Query that has values in column A that are not needed.

 

[vw412]

Suppose I do the merge of customer records at the same time as the copy? Would that work for you? Will you need to data from the old records (except column E) or just delete it?

 

[Elliottj2121]

All I need from the old records is column E. Everything else can be deleted in the old record.

 

[Elliottj2121]

Suppose I do the merge of customer records at the same time as the copy? Would that work for you? Will you need to data from the old records (except column E) or just delete it?

Yes that would work for me. I don't need the old data just column E

 

[vw412]

Working on this. In your CopyData routine you do a Find for "*" in formulas both in the wbCopy and the wbDest. Based on the images above the Find won't return anything. Can you help me understand this? Also, which workbook contains the VBA code?

 

[vw412]

My apologies, I just figured out about the Find question. Just goes to show we can all learn new ways of doing things.