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 ).


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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube