Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Wednesday, October 01, 2008 – Permalink –

Data Comparison

No formulas

The Data Consolidation technique allows you to compare lists quickly and easily.

With the Consolidation technique, you can identify the number of duplicate entries in two or more lists without using a formula.
(not that it's easier, just that there are no formulas)

Example:

In the sheet there are two lists: List 1 is in column A (in cells A2:A10), and List 2 is in column C (in cells C2:C10).
In Cell B1 type "List number".
In Cells B2:B10, enter the number 1.
In Cells D2:D10, enter the number 2.
Cut Cells C2:D10 and paste them into Cell A11.
Press Ctrl+*, then press Ctrl+F3, and enter a name for the list (such as Compare).
Select cell D4 or another worksheet.
From the Data menu, select Consolidate.
Select Count as the Function.
In the Reference box, press F3 and paste the Name you defined for the list.
Click Add.
Select both Top row and Left column "Use labels in" checkboxes.
Click OK.

The numbers appears in Column B are the totals of the list number in Column B.

If the result = 1, the name appears in List 1 and does not appear in List 2.
If the result = 2, the name appears in List 2 and does not appear in List 1.
If the result = 3, the name appears in both lists (1+2=3).

The action is not dynamic, so if you make changes, the Consolidation must be rerun.

From:
"Mr Excel ON EXCEL" (Holy Macro Books)

Also see:
John Walkenbach:
Comparing Two Lists With Conditional Formatting

Chip Pearson:
Duplicate And Unique Items In Lists

Here's a more complex method:
Microsoft Office Online:
Use Excel to compare two lists of data

Also:
BetterSolutions.com:
What are Consolidated Worksheets ?

See all Topics
excel
Labels: Formulas, Functions, Tips, Tutorials

<Doug Klippert@ 4:16 AM

Comments: Post a Comment