How do you create a mechanical compare in Excel

[tuccokeith]

Hello,

I want to create a macro that creates a compare routine that will do the following:

Compare two Excel worksheets

Create a third worksheet that highlights the rows and columns or cells that did not compare in worksheets 1 and 2.

Thanks

 

[xlbo]

And have you started on this or are you asking us just to create the whole thing for you ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tuccokeith]

I want to use the following type of logic for testing duplicate entries, however the example below only works if the data is into 2 ranges on the same sheet. I want to be able compare two unique worksheets:

Pearson Software Consulting, LLC

Duplicate And Unique Items In Lists

This page describes a variety of methods you can use when working with lists of data that may contain duplicate entries. First, we'll examine how to highlight or mark duplicate entries in a list using Excel97's Conditional Formatting feature. Next, we'll look at a way to count the number of unique entries in the range. Next, we'll discuss a worksheet formula to extract the unique elements that exist in a list. Finally, we'll learn how to compare two lists of data and extract the entries that occur on one list but not the other, or entries that exist on both lists.

For purposes of example, suppose we have two lists of data, named Range1 and Range2. These ranges contain the same number of rows, and start in the same row number. They need not be in adjacent columns, but they must be on the same worksheet. For example, we may define Range1 on Sheet1 as A5:A14 on Range2 as C5:C14. Blank cells are allowed anywhere within Range1 and Range2



Testing For Duplicate Entries

If you need to determine whether a list in Excel has duplicate entries, you can use the following formula. It will display "You Have Duplicates" if the list in Range1 has duplicate entries, or "No Duplicates" if the range does not have any duplicates.

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates&quot

This is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula, and when ever you edit it later. This formula requires that the complete range contain data. If only the first N cells contain data, and the rest are empty, the formula will return "Duplicates" because it considers the empty cells to be duplicates of themselves. If you want the formula to look only that the cells that contains data, use a formula like the following:

=IF(MAX(COUNTIF(INDIRECT(&quot;A2:A&quot;&(MAX((A2:A500<>&quot;&quot*ROW(A2:A500)))),INDIRECT(&quot;A2:A&quot;&(MAX((A2:A500<>&quot;&quot*ROW(A2:A500))))))>1,&quot;Duplicates&quot;,&quot;No Duplicates&quot

This formula will look only that the cells from A2 down to the last cell that contains data. This is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula, and when ever you edit it later. This formula requires that the complete range





Highlighting Duplicate Entries

Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional
Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the
Conditional Formatting tool from the Format menu: Format->Conditional Formatting.
Change the &quot;Cell Value Is&quot; option to &quot;Formula Is&quot; and enter the following formula in the
formula text box:

=IF(COUNTIF(Range1, A5)>1,TRUE,FALSE)

Where A5 is the first cell in Range1. Then, click the Format button and select the font or
color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be
formatted as you selected. For example, if &quot;Able&quot; occurs twice in Range1, both
occurrences of &quot;Able&quot; will appear highlighted.

 

[xlbo]

Using the same methodology, name the ranges, range1 and range2

then highlight range1 and go Format>Conditional format
Change to &quot;Formula Is&quot; and enter:

=NOT(ISNA(VLOOKUP(A1,range2,1,FALSE)))

where range1 starts in A1

Set your highlight colour et voila

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244