Tip Printed from ExcelTip.com

---

Comparing Lists


To compare Lists, use the Consolidate technique:

1. The screenshot below shows two Lists, the first in column A and the second in column D. Add the text List Number as a heading in columns B and E, and then add the List numbers, as shown.
2. Define Names for two Lists, and note the instructions on how to consolidate Lists.
3. Select a cell, and from the Data menu, select Consolidate.
4. In the Reference box, press F3.
5. In the Paste Name dialog box, select List1, click OK, and then click Add to add List1 to All references.
6. Press F3 again and add List2 to All references.
7. In Use Labels in, select the Top row and Left column checkboxes, and then click OK.

The consolidation results are shown in cells A10:B16. The consolidation technique uses SUM to total the numbers in the Lists when the text at the top row and in the leftmost column matches for 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.