Filter and merging data

Elliottj2121

New Member
Joined
Apr 15, 2021
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
[/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.

1619811481485.png



1619811562016.png
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 15, 2021
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 15, 2021
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 15, 2021
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Elliottj2121

New Member
Joined
Apr 15, 2021
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
My apologies, I just figured out about the Find question. Just goes to show we can all learn new ways of doing things.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,818
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top