# MS Excel Question

**URL:** <https://boards.straightdope.com/t/ms-excel-question/233506>\
**Category:** Factual Questions\
**Created:** [March 10, 2004, 4:37pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506 "2004-03-10T16:37:45Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Achilles](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@Achilles](https://boards.straightdope.com/u/Achilles)\
**Post date:** [March 10, 2004, 4:37pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/1 "2004-03-10T16:37:45Z")

</div>

This should be quite easy - I just don’t know what the answer is. What I need to be able to do is:

I have two spreadsheets that should be exact replicas of each other. There are c. 1250 rows in each, and about 12 columns in each. Some cells in each row are WORDS, some are NUMBERS.

I can cut and paste one sheet underneath the other, and then sort them by whatever column I wish. Now the two lots of data are merged together - and if all is well, there should be two, and exactly two identical versions of each row.

What I need is a way to quickly compare all the cells in the 1st row to the 2nd row - but no further. Then all the cells in the 3rd Row to the 4th row, the 5th to the 6th… and so on.

Can someone help me?

I’m sure that it can be done.

Thanks

---

<div class="post-metadata">

**Author:** ![Bean\_Counter](https://avatars.discourse-cdn.com/v4/letter/b/db5fbb/32.png) [@Bean\_Counter](https://boards.straightdope.com/u/Bean_Counter)\
**Post date:** [March 10, 2004, 5:12pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/2 "2004-03-10T17:12:59Z")

</div>

Do you have to copy one under the other?

My suggestion would be to copy and paste one of the spreadsheets to the right of the other. Then you would have one spreadsheet 1,250 rows by 24 columns. Then you could easily do =if(a1\<\>a13,“Oh why, God, WHY???”,“It’s all good”) or something along those lines in order to compare the values.

---

<div class="post-metadata">

**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\
**Post date:** [March 10, 2004, 5:49pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/3 "2004-03-10T17:49:06Z")

</div>

Why are you doing this? If you need two identical copies of the sheet, then:

note: the list needs column labels for this to work

copy one list below the other  
select the entire list

For the menu:  
Data  
Advanced Filter

In the resulting po-up box:  
Copy list to another location  
Unique records only  
Copy to some cell address not in the original list;

You should get a list of every individual row, including ones that are only in either one of the original lists.

Delete all the columns with the original duplicate list, and copy the sheet.

This gives you a single complete list with no duplicates.

If you need better instructions, post back.

---

<div class="post-metadata">

**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\
**Post date:** [March 10, 2004, 5:52pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/4 "2004-03-10T17:52:12Z")

</div>

Actually, it is a **pop-up**.

And copying the work-sheet will probably give you two complete lists with no duplicates.

---

<div class="post-metadata">

**Author:** ![Dynamic\_Mike](https://avatars.discourse-cdn.com/v4/letter/d/3be4f8/32.png) [@Dynamic\_Mike](https://boards.straightdope.com/u/Dynamic_Mike)\
**Post date:** [March 10, 2004, 6:56pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/5 "2004-03-10T18:56:23Z")

</div>

Instead of combining your data in one worksheet to do your comparison, I suggest leaving the data in two sheets and adding a third. Make a copy of one of your sheets, and replace its data with formulas to compare the cells of the other two (using IF functions similar to **Bean Counter** ’s suggestion).

Assuming your original worksheets are named “Sheet1” and “Sheet2”, the formula in cell A2 of the new sheet would be =IF(Sheet1!A2\<\>Sheet2!A2,“Not OK”,“OK”).

[aside]  
I’ve been lurking for years and years (back to the Wally era). Thanks **Achilles** for posting the GQ that finally drew me out.  
Rather anti-climactic, on preview.  
[/aside]

---

<div class="post-metadata">

**Author:** ![Achilles](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@Achilles](https://boards.straightdope.com/u/Achilles)\
**Post date:** [March 11, 2004, 8:52am UTC](https://boards.straightdope.com/t/ms-excel-question/233506/6 "2004-03-11T08:52:52Z")

</div>

> [@Dynamic Mike](#):
>
> Instead of combining your data in one worksheet to do your comparison, I suggest leaving the data in two sheets and adding a third. Make a copy of one of your sheets, and replace its data with formulas to compare the cells of the other two (using IF functions similar to **Bean Counter** ’s suggestion).
> 
> Assuming your original worksheets are named “Sheet1” and “Sheet2”, the formula in cell A2 of the new sheet would be =IF(Sheet1!A2\<\>Sheet2!A2,“Not OK”,“OK”).
> 
> [aside]  
> I’ve been lurking for years and years (back to the Wally era). Thanks **Achilles** for posting the GQ that finally drew me out.  
> Rather anti-climactic, on preview.  
> [/aside]

This looks good - I’ll give it a shot after my morning meeting - YAAAWN… 😉

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [March 11, 2004, 11:53am UTC](https://boards.straightdope.com/t/ms-excel-question/233506/7 "2004-03-11T11:53:33Z")

</div>

This useful macro will do it:

* * *

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)  
Dim r As Long, c As Integer  
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer  
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String  
Dim rptWB As Workbook, DiffCount As Long  
If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub  
If rng1.Areas.Count \> 1 Or rng2.Areas.Count \> 1 Then  
MsgBox “Can’t compare multiple selections!”, \_  
vbExclamation, “Compare Worksheet Ranges”  
Exit Sub  
End If  
Application.ScreenUpdating = False  
Application.StatusBar = “Creating the report…”  
Set rptWB = Workbooks.Add  
Application.DisplayAlerts = False  
While Worksheets.Count \> 1  
Worksheets(2).Delete  
Wend  
Application.DisplayAlerts = True  
With rng1  
lr1 = .Rows.Count  
lc1 = .Columns.Count  
End With  
With rng2  
lr2 = .Rows.Count  
lc2 = .Columns.Count  
End With  
maxR = lr1  
maxC = lc1  
If maxR \< lr2 Then maxR = lr2  
If maxC \< lc2 Then maxC = lc2  
If lr1 \<\> lr2 Or lc1 \<\> lc2 Then  
If MsgBox(“The two ranges you want to compare are of different size!” & \_  
Chr(13) & “Do you want to continue anyway?”, \_  
vbQuestion + vbYesNo, “Compare Worksheet Ranges”) = vbNo Then Exit Sub  
End If  
DiffCount = 0  
For c = 1 To maxC  
Application.StatusBar = “Comparing cells " & \_  
Format(c / maxC, “0 %”) & “…”  
For r = 1 To maxR  
cf1 = “”  
cf2 = “”  
On Error Resume Next  
cf1 = rng1.Cells(r, c).FormulaLocal  
cf2 = rng2.Cells(r, c).FormulaLocal  
On Error GoTo 0  
If cf1 \<\> cf2 Then  
DiffCount = DiffCount + 1  
Cells(r, c).Formula = “’” & cf1 & " \<\> " & cf2  
End If  
Next r  
Next c  
Application.StatusBar = “Formatting the report…”  
With Range(Cells(1, 1), Cells(maxR, maxC))  
.Interior.ColorIndex = 19  
With .Borders(xlEdgeTop)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
With .Borders(xlEdgeRight)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
With .Borders(xlEdgeLeft)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
With .Borders(xlEdgeBottom)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
On Error Resume Next  
With .Borders(xlInsideHorizontal)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
With .Borders(xlInsideVertical)  
.LineStyle = xlContinuous  
.Weight = xlHairline  
End With  
On Error GoTo 0  
End With  
Columns(“A:IV”).ColumnWidth = 20  
rptWB.Saved = True  
If DiffCount = 0 Then  
rptWB.Close False  
End If  
Set rptWB = Nothing  
Application.StatusBar = False  
Application.ScreenUpdating = True  
MsgBox DiffCount & " cells contain different data!”, \_  
vbInformation, “Compare Worksheet Ranges”  
End Sub

Sub TestCompareWorksheetRanges()

```
' compare two ranges in two different worksheets in two different workbooks
CompareWorksheetRanges ActiveWorkbook.Worksheets(1).Range("A1:O2000"), _
    Workbooks("OtherBook.xls").Worksheets(1).Range("A1:O2000")

```

## End Sub

First change OtherBook (second last line) to one of the names of your two workbooks and then copy the whole thing. Go to your **other** workbook and hit [Alt] F11. Then select the Folder Microsoft Excel Objects and from the menu choose Insert\>Module. Paste the macro in. Close it all up which will save it. If you like you can hit [Alt] F8 and select a shortcut key for the macro.

Now to run it just open both files go into the one containing the macro and run it. It will write a new workbook each time showing any differences between the files. If you go beyond 2000 rows adjust those numbers in the macro.

---

<div class="post-metadata">

**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\
**Post date:** [March 11, 2004, 12:46pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/8 "2004-03-11T12:46:48Z")

</div>

don’t ask:

In your macro, won’t missing a single row in one sheet cause ALL the following rows to be in the final report?

---

<div class="post-metadata">

**Author:** ![Achilles](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@Achilles](https://boards.straightdope.com/u/Achilles)\
**Post date:** [March 11, 2004, 1:09pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/9 "2004-03-11T13:09:00Z")

</div>

**don’t ask** - that is a great macro ! Thanks. by the way, you’re missing an “end if”, but appart from that - spot on !!!

What exactly does “wend” do - I cannot figure out what it means.

Thanks anyway !

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [March 11, 2004, 1:10pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/10 "2004-03-11T13:10:20Z")

</div>

Well of course if an extra blank row has been inserted. It is just checking each cell against the same address in the other workbook so inserting a bogus row will throw it off. If one row is blank but should have entries it will just report that row.

---

<div class="post-metadata">

**Author:** ![dylan\_73](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@dylan\_73](https://boards.straightdope.com/u/dylan_73)\
**Post date:** [March 11, 2004, 1:19pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/11 "2004-03-11T13:19:24Z")

</div>

While Worksheets.Count \> 1  
Worksheets(2).Delete  
Wend

“Wend” is the end of the While loop.

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [March 11, 2004, 1:19pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/12 "2004-03-11T13:19:33Z")

</div>

> [@Achilles](#):
>
> **don’t ask** - that is a great macro ! Thanks. by the way, you’re missing an “end if”, but appart from that - spot on !!!
> 
> What exactly does “wend” do - I cannot figure out what it means.
> 
> Thanks anyway !

**wend** ends a **while** loop.

It isn’t actually my work I just have it in a library of useful odds and ends we hand around at work. It’s surprising how often someone at work says “If only you could…” and someone else will say “I have a macro that does that.”

---

<div class="post-metadata">

**Author:** ![Achilles](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@Achilles](https://boards.straightdope.com/u/Achilles)\
**Post date:** [March 11, 2004, 1:31pm UTC](https://boards.straightdope.com/t/ms-excel-question/233506/13 "2004-03-11T13:31:33Z")

</div>

> [@don't ask](#):
>
> **wend** ends a **while** loop.
> 
> It isn’t actually my work I just have it in a library of useful odds and ends we hand around at work. It’s surprising how often someone at work says “If only you could…” and someone else will say “I have a macro that does that.”

Well thanks anyway. !!! 🙂

You might want to mention that they are missing and “end if”…

Thanks again