Appending data and removing duplicates

jardenp

Active Member
Joined
May 12, 2009
Messages
367
I keep a master sheet of transaction data in Master.xlsx. Column A is the unique identifier. I regularly pull new transaction data that I want to append to the Master list. The transactions in the incoming list may be totally new (new value in column A) or they may be updates of transactions already in the list. I'm trying to figure out a VBA solution to bring in the new transactions and have the updated transactions replace the older versions. This is a variation of removing duplicates, but that function (as far as I know) always keeps the duplicate with the lowest row number (i.e., topmost on the spreadsheet). Row number is irrelevant, so I'm not asking for a solution that would necessarily keep the same row order.

As an example:

Master:
Trans#Data1Data2
U100AA
U101AA
U102AA
U103AA

<tbody>
</tbody>

Then bring in A2:C4 of the current sheet. A2:C4 is:
U104BB
U105BB
U101BB

<tbody>
</tbody>

This would leave Master.xlsx as:
Trans#Data1Data2
U100AA
U102AA
U103AA
U104BB
U105BB
U101BB

<tbody>
</tbody>

(or if order was preserved (which it doesn't need to be)):
Trans#Data1Data2
U100AA
U101BB
U102AA
U103AA
U104BB
U105BB

<tbody>
</tbody>

Please help! Is there such a thing as Remove Duplicates that starts at the bottom and looks up?

There is no time stamp or other data point I can sort by to put the most recent transaction version on top.

I know I could add a column that brought in the "uploading" time and sort by that, but I'd prefer a more elegant solution and one that doesn't require changing the data set. If I need to, I'll do that, but I know from repeated experience that this is the place for solid Excel/VBA genius.

Thanks!

JP in IN
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This starts at the bottom and will replace the bottom most entry. This assumes the transaction list is a separate file (Workbook) in worksheet format in the same column order as the Master file. You will need to save your Master.xlsx file as a macro enabled workbook, and if not already done, insert a code module in the VB Editor. Access the VB Editor by pressing Alt + F11, and if the large pane in the VBE is dark, then click Insert on the VBE menu bar, then click Module and the screen should brighten. Copy the code below into the code module. Check the design mode icon to be sure it is not highlighted before closing the VBE.
Code:
Sub update()
Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, fn As Range
Set wb = Workbooks(2) 'Edit workbook name
Set sh1 = ThisWorkbook.Sheets(1) 'Edit sheet name
Set sh2 = wb.Sheets(1) 'Edit sheet name - See comment below about naming the book, same applies to worksheet.
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr 'Assumes header on new transactions sheet.
        With sh2
            Set fn = sh1.Range("A:A").Find(.Cells(i, 1).Value, .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious)
                If Not fn Is Nothing Then
                    .Cells(i, 1).EntireRow.Copy fn
                End If
        End With
    Next
End Sub
You can use the index number for the second workbook to avoid having to change the name if it does not arrive with the same name each time, BUT you must make sure that it and the Master workbook are the only two open and that the Master workbook is opened first. Excel assigns the index based on the order in which they are opened. Sheets are indexed based on their name tab sequence from left to right.
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Thank you! You are right in your assumptions about same column structure and headers.

I was planning on using this as part of another sub that would be stored in common XLSB that's used to share macros, so I can't store it with the Master.xlsx. I'm afraid to put a shared code out there that would require no other workbooks be open. Can I adapt this to run on the current workbook? My general plan was to copy the transaction data, open the Master.xlsx, paste the data to last row + 1, get rid of duplicates from the bottom up, save Master.xlsx, close Master.xlsx, and then return to my original workbook. I'm not asking for code to do all that--I can figure out everything but the get rid of duplicates from the bottom up. Can I use the heart of your code to do that?

(In case it's not obvious, the mechanics of what you laid out are above my head!)
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
this should run from a third workbook. But now it will be mandatory that the workbook names be used instead of the index numbers to avoid undesired results.
Code:
Sub update()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, fn As Range
Set wb1 = Workbooks(1) 'Edit workbook name-this would be the master
Set wb2 = Workbooks(2) 'Edit workbook name
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name - See comment below about naming the book, same applies to worksheet.
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr 'Assumes header on new transactions sheet.
        With sh2
            Set fn = sh1.Range("A:A").Find(.Cells(i, 1).Value, .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious)
                If Not fn Is Nothing Then
                    .Cells(i, 1).EntireRow.Copy fn
                End If
        End With
    Next
End Sub
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367

ADVERTISEMENT

Thank you so much for your help!
 

AMDC

New Member
Joined
Dec 18, 2019
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have the same question here. I have tried this code, but nothing happens. Are the new transactions already appended to the master file (sheet 1) by the time the VBA runs?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
@AMDC-Welcome to the forum. If you start your own thread and describe what you want to do, you will get better results from the responders on the forum. Posting questions that piggyback other member's posts is not recommended, since their data layout and goals are likely different than yours. If they were really the same then the code would work. Try starting a new thread with your own problem description.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,541
Members
416,923
Latest member
jarri

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