Comparing columns

[Carlie]

I have a worksheet that needs to be updated using data from another
worksheet. I need to write a macro that compares data from column A in
the first worksheet and updates column A in the next worksheet.The
layout of both sheets are very similar, column headings are the same.
However, column A in both sheets contains unmatched data e.g.

Worksheet1

A1 Roses
A2 Daisies
A3 Lillies
A4 Daffoldils
A5 Tulips

Worksheet2 ( This will be updated and contain the macro)
A1 Roses
A2 Daisies
A3 Asters
A4 Daffoldils
A5 Sunflowers

Does anyone have any tips or code for this macro. Many thanks.

 

[Dave Peterson]

What do you want to do after you find a difference?

If you've only got one column in each sheet and you want a list that has exactly
one entry (no matter which sheet they came from), you could just copy the cells
to one column (include one header row).

Then use data|Filter|advanced filter to extract the unique entries.

Debra Dalgleish shares some instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

You could record a macro when you do it manually.

===========
If you have multiple columns in each sheet, you could get this combined list of
unique entries and then use a bunch of =vlookup()'s to retrieve various columns
from each sheet.

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[Carlie]

I'm not sure if a vlookup would be suitable as the workbook that needs
updating is a template with approx. 24 field headings and 60 string
values in column A ( the data between b4:z63 has been cleared) In this
template there are also several other worksheets e.g graphs/charts that
will be populated once the template is running with the macro.

In the other workbook the headings are exactly the same as the template
however, column A is not an exact match with column A in the template.
(Cells b10:z63 contains data to be updated in the template)

After the main workbook searches for a match it should copy the entire
row of data and paste it into the template. If a match is found with
blank data it should also copy and paste. But if a match is not found
it should skip that row and continue searching until another match is
found.

I am not a programmer but I have tried a bit of VBA below. please help
as it does not run properly.

Dim LMarketV As String
Dim LColumn As Integer
Dim LFound As Boolean


'Open P1 Country Workbook
Workbooks.Open Filename:= _
"G:\Eworking\IC\TPS\Lehman POINT\Input
templates\P1.ms.country.csv"

'Retrieve market value to search for
LMarketV = Sheets("P1.ms.country").Range("B9").Value


'Start at column B
LColumn = 2
LFound = False

While LFound = False

'Encountered blank cell in row 2, resume search
If Len(Cells(2, LColumn)) = 0 Then

Resume Next
Exit Sub

'Found match in row 2
ElseIf Cells(2, LColumn) = LMarketV Then

'Select values to copy from "P1.ms.country" worksheet
Sheets("P1.ms,country").Select
Range("B10:Z63").Select
Selection.Copy

'Paste onto "Template V2" workbook
Windows("Template v2.xls").Activate
Cells(4, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

LFound = True

'Continue searching
Else
LColumn = LColumn + 1
End If

Wend


End Sub

 

[Dave Peterson]

You have two workbooks that need to be compared.

WorkbookM (master) and workbookT (template)

And you want each cell in A4:A63 in sheet1 of workbookM to look for a match in
sheet1 of workbookT.

If a match is found, what happens to each column of that matching row?

If it's empty, is it replaced with values from workbookM?
If it's filled in, is it replaced with values from workbookM?

If there is no match, where would this row from workbookM be placed in
WorkbookT? At the bottom A64?

And when you say column A doesn't match exactly, does that mean that there is
never an identical value in the column or that some of the values don't match?

 

[Carlie]

If a match is found from column A in WorkbookM the entire row is copied
and pasted into the matching row of WorkbookT.

If there are empty cells (not in column A) between B4:Z63 in workbookM
they are also copied and pasted into workbookT

Additionally, if there isn't a match I would like to copy and paste at
the bottom in workbookM A64.

Finally, there are approx 10 values that do not match in column A.

 

[Dave Peterson]

This copies the whole row (instead of B:Z). But it wouldn't be a problem to
change if it mattered:

Option Explicit
Sub testme()

Dim WksM As Worksheet
Dim WksT As Worksheet
Dim RngM As Range
Dim RngT As Range
Dim DestCell As Range
Dim res As Variant
Dim myCell As Range

'change as required--and make sure the workbooks are open
Set WksM = Workbooks("book1.xls").Worksheets("Sheet1")
Set WksT = Workbooks("book2.xls").Worksheets("sheet1")

Set RngM = WksM.Range("a4:a63")
Set RngT = WksT.Range("a4:a63")

Set DestCell = WksT.Range("a64")

For Each myCell In RngM.Cells
If myCell.Value = "" Then
'do nothing
Else
res = Application.Match(myCell.Value, RngT, 0)
If IsNumeric(res) Then
'found a match
myCell.EntireRow.Copy _
Destination:=RngT(res)
Else
'no match
myCell.EntireRow.Copy _
Destination:=DestCell
'get ready for next non-match
Set DestCell = DestCell.Offset(1, 0)
End If
End If
Next myCell

End Sub

 

[Carlie]

The coding was perfect.

Many thanks for your help Dave