PDA

View Full Version : Compare new sheet vs old and extract data from old sheet into new sheet.



AndersW
07-03-2017, 01:27 PM
Hi
I have two Workbooks. One old large Workbook with almost 4000 lines in the row. And an new one with almost 4500 lines.
OldWorkbook:
Text in Column A and B is unique and belongs together.
The text in C is either empty or a more thorough explenation of what is written in B.
The check marks in D to H is done so I know who should have the text and id. And also so Not everyone needs to read through all the information in A and B.

But unfortunatly know I have a newWorkbook:
The new one only have information in A and B column. But not all of them are the same as in the old Workbook.
Some of them is also rewritten in the text field.
This sheet does not have any of the information in C to H column.

So I am trying to have at vba that can check the new workbook against the old one. If A and B in the new matches the A and B somewhere down the column. It shall copy all the added text and options in C to H from the old to the new one.
Additionaly I need it to create a new check mark in I column if A and B was the same in both sheets.
In that way I will end up with all the added information from the old workbook into the new one as long as nothing was changed.
And then I can later filter with the check mark in I column and go over the ones that did not match and verify and add information to them in the end. This will save me from maybe checking 3800 of the 4500 lines.

I have attached some simple examples of the workbooks to show about how they look like.

1967019671

mdmackillop
07-03-2017, 03:12 PM
Paste the code into NewWorkbook and run from there

Sub test()
Dim wsOld As Worksheet
Dim wsNew As Worksheet
Dim data As Range


Set wsNew = Sheet1
Set wsOld = Workbooks("OldWorkbook.xlsx").Sheets(1)
With wsOld
Set data = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cel In data
Set c = wsNew.Columns(1).Find(cel, lookat:=xlWhole)
If Not c Is Nothing Then
If c.Offset(, 1) = cel.Offset(, 1) Then
c.Offset(, 8) = "x"
c.Offset(, 2).Resize(, 6).Value = cel.Offset(, 2).Resize(, 6).Value
End If
End If
Next cel
End Sub

YasserKhalil
07-03-2017, 11:20 PM
Cross-Post at this link
https://www.excelforum.com/excel-programming-vba-macros/1191540-compare-new-sheet-vs-old-and-extract-data-from-old-sheet-into-new-sheet.html

mdmackillop
07-08-2017, 08:12 AM
Pleae read our FAQ and advise when you have a solution.