Find duplicates across 2 workbooks and edit duplicate records

J

jmsantoro71

New Member
Joined
Apr 29, 2016
Messages
11
<small style="box-sizing: border-box; -webkit-font-smoothing: antialiased; font-size: 11.04px; text-transform: uppercase; display: block; color: rgb(105, 109, 111); font-family: 'Helvetica Neue', roboto, Arial, sans-serif; -webkit-tap-highlight-color: transparent !important; outline: 0px !important; background-color: rgb(238, 242, 244);"><small style="box-sizing: border-box; -webkit-font-smoothing: antialiased; font-size: 11.04px; display: block; -webkit-tap-highlight-color: transparent !important; outline: 0px !important;">I'm new to VBA and this code is kicking my tail. I've been updating the price sheets one record at a time but am trying desperately to write code I can use for each new update. Will someone please help???? Here's what I've got...

I HAVE TWO WORKBOOKS. ONE NAMED "MASTER" AND ANOTHER NAMED "UPDATE". THEY ARE THE SAME IN REGARDS TO STRUCTURE AND FORMAT. ONLY SOME OF THE DATA IS DIFFERENT. IN BOTH WORKBOOKS, COLUMN A CONTAINS MODEL NUMBERS. COLUMNS K & L CONTAIN CORRESPONDING PRICES FOR THOSE MODEL NUMBERS IN COLUMN A. THE SAME EXISTS FOR COLUMN N (MODEL NUMBERS) AND COLUMNS X & Y (PRICES). IN BOTH WORKBOOKS ALL PRICES ARE EITHER (FONT COLOR) BLACK, RED OR BLUE. THE FILE IS FORMATTED TO PRINT AS A PRICE SHEET. THEREFORE, THE DATA FIELDS (MODEL NUMBER AND CORRESPONDING INFO) IS DUPLICATED (THE FIELDS, NOT THE DATA) ON THE WORKSHEET IN ORDER TO GET MORE PER PRINTED PAGE INSTEAD OF HAVING EACH DATA FIELD LIMITED TO ONE COLUMN EACH.

MY TASK IS TO COMPARE THE TWO WORKBOOKS LOOKING FOR MODEL NUMBERS IN THE "UPDATE" WORKBOOK WHICH ARE ALSO FOUND IN "MASTER". (COLUMNS A & N IN BOTH WORKBOOKS) WHEN A DUPLICATE MODEL NUMBER IS DETECTED, USING THE "MASTER" MODEL NUMBER, CHECK THE PRICE IN COLUMN K. IF IT IS BLACK, DO NOTHING. IF IT IS RED OR BLUE, COPY IT AND PASTE IT ON THE "UPDATE" WORKBOOK IN COLUMN K IN THE ROW OF THE CORRESPONDING MODEL NUMBER. THEN, DO THE SAME FOR THE PRICE IN COLUMN L. THE SAME PROCESS WOULD BE APPLIED TO THE MODEL NUMBERS IN COLUMN N AND THE CORRESPONDING PRICES IN COLUMNS X & Y. SO, THE END RESULT WOULD BE... FROM THE MASTER WORKBOOK ANY PRICES WHICH ARE RED OR BLUE WOULD BE INSERTED INTO THE CORRECT LOCATION IN THE UPDATE WORKBOOK. ANY BLACK PRICES IN THE MASTER WORKBOOK ARE LEFT ALONE. HERE IS A SAMPLE OF WHAT IT LOOKS LIKE...

</small></small>[TABLE="class: cms_table, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Model[/TD]
[TD]Desc 1[/TD]
[TD]Desc 2[/TD]
[TD]Mfg[/TD]
[TD]Series[/TD]
[TD]Code 1[/TD]
[TD]Code 2[/TD]
[TD]Code 3[/TD]
[TD]Code 4[/TD]
[TD]Code 5[/TD]
[TD]B Price[/TD]
[TD]A Price[/TD]
[TD][/TD]
[TD]Model[/TD]
[TD]Desc 1[/TD]
[TD]Desc 2[/TD]
[TD]Mfg[/TD]
[TD]Series[/TD]
[TD]Code 1[/TD]
[TD]Code 2[/TD]
[TD]Code 3[/TD]
[TD]Code 4[/TD]
[TD]Code 5[/TD]
[TD]B Price[/TD]
[TD]A Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]cpx43w[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD]435[/TD]
[TD][/TD]
[TD]gft43th[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]665[/TD]
[TD]546[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]gxf4ts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]321[/TD]
[TD]345[/TD]
[TD][/TD]
[TD]ggt45d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]398[/TD]
[TD]776[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]htf45ty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]435[/TD]
[TD]765[/TD]
[TD][/TD]
[TD]we324s[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]458[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]pdf12ys[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]542[/TD]
[TD]356[/TD]
[TD][/TD]
[TD]rd4432[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]869[/TD]
[TD]775[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]dd34rtd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1900[/TD]
[TD]1965[/TD]
[TD][/TD]
[TD]trh556[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]334[/TD]
[TD]356[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ct35ddr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2344[/TD]
[TD]3211[/TD]
[TD][/TD]
[TD]th99yth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]545[/TD]
[TD]345[/TD]
[/TR]
</tbody>[/TABLE]

<small style="box-sizing: border-box; -webkit-font-smoothing: antialiased; font-size: 11.04px; text-transform: uppercase; display: block; color: rgb(105, 109, 111); font-family: 'Helvetica Neue', roboto, Arial, sans-serif; -webkit-tap-highlight-color: transparent !important; outline: 0px !important; background-color: rgb(238, 242, 244);"><small style="box-sizing: border-box; -webkit-font-smoothing: antialiased; font-size: 11.04px; display: block; -webkit-tap-highlight-color: transparent !important; outline: 0px !important;">
ABOVE IS AN EXAMPLE OF THE MASTER WOORKBOOK. THE UPDATE WORKBOOK IS FORMATTED EXACTLY THE SAME EXCEPT ALL PRICING IS BLACK (FONT COLOR). IF A MODEL NUMBER IS FOUND IN THE UPDATE WORKBOOK WHICH IS ALSO IN THE MASTER WORKBOOK, AND THE PRICES FOR THAT MODEL NUMBER IN THE MASTER WORKBOOK ARE RED OR BLUE THOSE PRICES (AND FONT COLOR) NEED TO BE COPIED AND PASTED OVER THE PRICES IN THE UPDATE WORKBOOK. IF THE FONT COLOR OF THE PRICES IN THE MASTER WORKBOOK ARE BLACK, NOTHING IS DONE TO THE PRICES IN THE UPDATE FOR THAT MODEL.</small></small>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
assumes both workbooks open at runtime.
Code: 
Sub updates()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, rng1 As Range, rng2 As Range
Dim lr As Long, Mrng As Range, rng3 As Range, rng4 As Range, Urng As Range
Set wb1 = Workbooks("Master")
Set wb2 = Workbooks("Update")
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng1 = sh1.Range("K2:L" & lr)
Set rng2 = sh1.Range("X2:Y" & lr)
Set Mrng = Union(rng1, rng2)
Set rng3 = sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
Set rng4 = sh2.Range("N2", sh2.Cells(Rows.Count, 14).End(xlUp))
Set Urng = Union(rng3, rng4)
    For Each c In Mrng
        If c.Font.Color = vbBlue Or c.Font.Color = vbRed Then
            With sh2
                Set fn = Urng.Find(Trim(sh1.Cells(c.Row, 1).Value), , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    sh2.Cells(fn.Row, c.Column) = c.Value
                End If
            End With
        End If
    Next
End Sub
You will need to validate the sheet names where I have used sheet index numbers.
 
Upvote 0
Thank you so much for the code. I pasted it into a module in the update workbook while both workbooks were open. I also replaced (1) with ("Sheet1") where you indicated for me to edit the sheet names. However, when I run the module, nothing appears to happen. What am I missing???

Thank you again for your help. I am new to VBA and am progressing, but this code is way above my pay grade. I really appreciate your help.
 
Upvote 0
The code really is not that complicated. It uses a lot of variables to hold references to data locations, etc., but the execution is quite simple. It goes through you price ranges, cell by cell, looking for a red or blue font color. If it finds one, it then copies that value from that location to the same column on a row where a match is found for the value in column A of the sheet in the Master workbook. It worked without error in my test set up, so the only thing I can think of is the that the variables are not initializing properly. The code should be installed in code Module1 or any numbered code module that you have, but not a sheet or ThisWorkbook code module. You can check the variable values by stepping through the code using the F8 function key. Open the vb editor to where the code resides and left click once anywhere in the body of the code to activate the cursor there. Then press F8 function key and the first line of code should highlight in yellow. You can then continue to press the F8 key to step through the executable part of the code one line at a time. If you resize the vb editor screen you can overlay it on the Excel sheet to see if changes occur on the sheet as the code executes. You can also use the mouse pointer in the code module to hover over variables and see their values in the intellisense pop-up. This is a troubleshooting technique that I used to find problems in code. If you have the code in the correct type of code module, it should do something, even if it throws an error.

If the font color is set by conditional formatting then the code fails.
 
Last edited:
Upvote 0
Ok. I was mistaken. It was working when I initially tried it. There are two issues... The first - When the code changes prices on the update workbook, it doesn't carry over the font color from the Master. (I apologize if that requirement wasn't clear initially.) Second - It seems to copy most of the prices correctly. However, when I ran the module on about 100 test records, it copied almost all of the pricing correctly. Unfortunately, on a few of the records it copies the wrong pricing. I'm trying to see if I can find a pattern or rhyme or reason to the incorrect pricing. Is it possible for me to email you my test files and see if you can figure out what it's doing?
 
Upvote 0
jmsantoro71 said:
Ok. I was mistaken. It was working when I initially tried it. There are two issues... The first - When the code changes prices on the update workbook, it doesn't carry over the font color from the Master. (I apologize if that requirement wasn't clear initially.) Second - It seems to copy most of the prices correctly. However, when I ran the module on about 100 test records, it copied almost all of the pricing correctly. Unfortunately, on a few of the records it copies the wrong pricing. I'm trying to see if I can find a pattern or rhyme or reason to the incorrect pricing. Is it possible for me to email you my test files and see if you can figure out what it's doing?
Click to expand...
I can help you with the font color.
Change this
Code: 
If Not fn Is Nothing Then
    sh2.Cells(fn.Row, c.Column) = c.Value
End If
To this
Code: 
If Not fn Is Nothing Then
    c.Copy
    sh2.Cells(fn.Row, c.Column).PasteSpecial xlPasteValues
    sh2.Cells(fn.Row, c.Column).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False            
End If
I can't think of a reason for the wrong price because the value it pastes is the same value it found that met the color criteria. If your destination sheet and source sheet use different columns for the price range, then that could be a problem.
I don't accept off line projects by email. All of my volunteer suggestions are conducted on line so all can see the process and results. Sorry.
 
Upvote 0
Thank you again for all the help. I completely understand about off-line assistance. The new code corrected the font color issue. It works perfectly!!! In regards to pasting the wrong prices, it seems to be related to model numbers which are in column A on the Master, but appear in column N on the Update or vise versa. For example, Model CK7000SH is in A62 with prices in K62 & L62. On the update, that model number is found in N18 with prices in X18 & Y18. The code copied the correct price from the master but pasted it in K18 & L18 instead of X18 & Y18. It works great for any model numbers which appear in the same column on both workbooks, but not if the model number is in different columns on the two workbooks. Any ideas?
 
Upvote 0
I have reworked the code because it was not searching for model numbers in column N of the master, it was only doing column A model numbers. The problem with you priciing is that you have used a different color index for the prices in column K and L than in X and Y . That is a big problem with using colors as a flag, they look the same to the user but Excel knows the difference. The vbBlue equates to color index 5 and vbRed equates to color index 3. The blue in columns X and Y are color index 56. They might even be different in your original file. One way to fix it is to copy from a cell in column K then PasteSpecial - Formats in the ranges in columns X and Y. You only need to copy from one cell and you can paste special to all contigent cells of the same color.
Here is the new code.

Code: 
Sub updates()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, rng1 As Range, rng2 As Range
Dim lr As Long, Mrng As Range, rng3 As Range, rng4 As Range, Urng As Range, offst As Long
Set wb1 = Workbooks(1) '("Master")
Set wb2 = Workbooks(2) '("Update")
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng1 = sh1.Range("K2:L" & lr)
Set rng2 = sh1.Range("X2:Y" & lr)
Set Mrng = Union(rng1, rng2)
Set rng3 = sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
Set rng4 = sh2.Range("N2", sh2.Cells(Rows.Count, 14).End(xlUp))
Set Urng = Union(rng3, rng4)
    For Each c In Mrng
        Select Case c.Column
            Case 11, 24
                offst = 10
            Case 12, 25
                offst = 11
        End Select
        If c.Font.Color = vbBlue Or c.Font.Color = vbRed Then
                Set fn = Urng.Find(Trim(c.Offset(, -offst).Value), , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    c.Copy
                    fn.Offset(, offst).PasteSpecial xlPasteValues
                    fn.Offset(, offst).PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End If
        End If
    Next
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
141
Peter_SSs
Peter_SSs
A
  • Solved
Merging duplicates items after words contains colon based on different price for the same ID
2 3 4
Replies
39
Views
670
Peter_SSs
Peter_SSs
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
Nlhicks
  • Solved
Run my master workbook and call another non-macro enabled workbook and do the edits to it prior to saving
7 8 9
Replies
85
Views
4K
Nlhicks
Nlhicks
M
  • Question Question
Create new workbook for every sheet in active workbook, starting from Sheet(3) and saving them to a user specified folder location.
Replies
0
Views
999
Mars84
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top