» Comparing Lists in Excel 2007

In the screenshot below see two Lists, first list in range A2:A5, second list in range D2:D6.

The problem:

How to compare lists to find items appearing in one list only, and match items appearing in both lists.

To compare Lists, Consolidate lists:

1. Add the text List Number as a heading in columns B and E, and then add the List numbers, as shown in the screenshot.
2. Define Names for two Lists, and follow the instructions on how to Consolidate Lists (see Consolidating Lists in Excel 2007 ).

Results:

The consolidation results are shown in cells A8:B14. The consolidation uses the SUM function to total the numbers in the Lists when the text at the top row and in the leftmost column matches for both Lists.
The numbers in range B9:B14:
1= Name appears in List1 only.
2= Name appears in List2 only.
3= Name appears in both lists.



To compare more than two Lists:

Use 2^ to add the List number, or any other combination that returns the same results.
For List1, use the number 1, for List2, use the number 2, and for List3, use the number 4.
The result of consolidating three Lists:


The numbers in range B9:B15:
1= Name appears in List1 only.
2= Name appears in List2 only.
3= Name appears in both lists 1+2.
4= Name appears in List3 only.
5= Name appears in both lists 1+3.
6= Name appears in both lists 2+3.
7= Name appears in all three lists.