Appending data and removing duplicates

[jardenp]

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:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Trans#[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[/TR]
[TR]
[TD]U100[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U101[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U102[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U103[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

Then bring in A2:C4 of the current sheet. A2:C4 is:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]U104[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U105[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U101[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

This would leave Master.xlsx as:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Trans#[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[/TR]
[TR]
[TD]U100[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U102[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U103[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U104[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U105[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U101[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

(or if order was preserved (which it doesn't need to be)):
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Trans#[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[/TR]
[TR]
[TD]U100[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U101[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U102[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U103[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]U104[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]U105[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[JLGWhiz]

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.

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]

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]

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.

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]

Thank you so much for your help!

 

[JLGWhiz]

Thank you so much for your help!

You're welcome,
Regards, JLG

 

[AMDC]

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]

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