Merging?

[Jim Thompson]

Another !@#$%&* Girl Scout related problem...

Excel '97

TWO registrars (my wife and another woman) listing troops and girls on
two _separate_ spreadsheets :-(

Fortunately both sheets have the same formatting of rows and columns.

However there ARE duplications :-(

How does one _easily_ merge both sheets ??

Thanks!

...Jim Thompson
--
| James E.Thompson, P.E. | mens |
| Analog Innovations, Inc. | et |
| Analog/Mixed-Signal ASIC's and Discrete Systems | manus |
| Phoenix, Arizona 85048 Skype: Contacts Only | |
| Voice480)460-2350 Fax: Available upon request | Brass Rat |
| E-mail Icon at http://www.analog-innovations.com | 1962 |

I love to cook with wine Sometimes I even put it in the food

 

[Dave Peterson]

I don't think "easily" should be in that question.

Depending on the number of columns/fields, I'd create a third worksheet and get
a unique list of the key values (Names??? Do girl scouts have an id number???).

Copy the unique key (and a single header row) to column A of this new
worksheet. Copy the data from the second worksheet under that data.

Then use data|filter|advanced filter to get a unique list.

See Debra Dalgleish's site for more details.
http://contextures.com/xladvfilter01.html#FilterUR

Then for each field, I'd use 3 columns.

The first column would be an =vlookup() to retrieve the value from the first
worksheet.

The second column would be an =vlookup() to retrieve the value from the second
worksheet.

Then I'd convert those formulas to values (copy|paste special|values). Then get
rid of the #n/a's (edit|replace) and the 0's (for values from empty cells
brought back).

I'd try to make that =vlookup() as dumb as possible -- no error checking at
all. Just fix the results later.

Then I'd use the third column to see if there was a difference.

=b2=c2
(and the like)

Then use a final column to count the differences.
=countif(b2:xxx2,false)

Then apply a filter to show the rows that are non-zero in that last field.

Then comes the difficult part. Deciding who typed in the right stuff and which
entry should be used.

 

[Dave Peterson]

Or maybe a macro (there were two very similar questions)...

Maybe you could use a macro:

Option Explicit
Sub testme()

Dim MstrWks As Worksheet
Dim UpdWks As Worksheet
Dim MstrKey As Range
Dim UpdKey As Range
Dim UpdCell As Range
Dim res As Variant
Dim DestRow As Long

Set MstrWks = Workbooks("Book1.xls").Worksheets("Sheet1")
Set UpdWks = Workbooks("Book2.xls").Worksheets("sheet1")

With MstrWks
'remove any fill color--you'll be able to
'see the differences
.Cells.Interior.ColorIndex = xlNone
Set MstrKey = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With UpdWks
Set UpdKey = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each UpdCell In UpdKey.Cells
res = Application.Match(UpdCell.Value, MstrKey, 0)
If IsError(res) Then
'no match
With MstrWks
DestRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
Else
DestRow = res
End If

'now look at each field
With MstrWks
'are the values to compare in the same columns?
'I assumed they are, but they don't need to be!

'---------------------
'this portion will need to be repeated (or some loop)
'for each pair of fields that should be compared
'including the key field, too!
If .Cells(DestRow, "a").Value _
= UpdWks.Cells(UpdCell.Row, "a").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "a")
.NumberFormat = UpdWks.Cells(UpdCell.Row, "a").NumberFormat
.Value = UpdWks.Cells(UpdCell.Row, "a").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'- - - - - - - -
If .Cells(DestRow, "b").Value _
= UpdWks.Cells(UpdCell.Row, "b").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "b")
.NumberFormat = UpdWks.Cells(UpdCell.Row, "b").NumberFormat
.Value = UpdWks.Cells(UpdCell.Row, "b").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'----------------------
End With
Next UpdCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)