Finding only one value when comparing two workbooks

Status
Not open for further replies.

PGNewbie

New Member
Joined
Feb 6, 2020
Messages
41
Office Version
  1. 365
Platform
  1. Windows
I am using two workbooks to search for missing information that is in one book but not in the other. My code works but in some instances I have multiple matches being returned and only one value is entered in to the cell. I need to be able to add the first match to the existing row in workbook1 and then create a new row for each additional match and copy over columns "B", "C" and "D" from workbook2 on to workbook1, underneath the original row in workbook1, leaving column "A" empty for each new row

This is the code that I am using

PGNewbie()
Dim w1 As Worksheet, w2 As Worksheet
Dim wbnew As Workbook
Dim c As Range, FR As Variant
Dim d As Range
Dim e As Range

Application.ScreenUpdating = False


Set w2 = Workbooks("Book2.xlsx").ActiveSheet
Set w1 = Workbooks("Book1.xlsx").ActiveSheet



For Each c In w1.Range("C2", w1.Range("C" & Rows.Count).End(xlUp))
FR = Application.Match(c, w2.Columns("C"), 0)
If IsNumeric(FR) Then
c.Offset(, 1).Value = w2.Range("D" & FR).Value
End If

Next c

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Is this a duplicate of this thred
 
Upvote 0
Is this a duplicate of this thred
:) Kind of, I was working with my original code not the concatenate code and assumed I needed to have a different thread as it is technically a different question
 
Upvote 0
Are both threads looking to do the same thing?
 
Upvote 0
Ok I will close this thread as we do not allow duplicates.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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