Chapter 21: Consolidating Data
Author: Joseph Rubin, CPA www.exceltip.com
If the result = 1, the name appears in List 1 and does not appear in List
If the result = 2, the name appears in List 2 and does not appear in List
If the result = 3, the name appears in both lists (1+2=3).
Comparing Three or More Lists
Using the technique described above, paste these lists one list below the
Note – the list number should be factors of 2 (or any other mathematical
combination that you choose).
For List 1, use 2^0 or the number 1. For List 2, use 2^1 or the number 2.
For List 3, use 2^2 or the number 4.
The consolidation results in a series of numbers from 1 to 7, in which:
1,2,4 = the name appears in only one list.
3,5,6 = the name appears in two lists, 3=2+1, 5=4+1,6=2+4.
7 = the name appears in all three lists.
Consolidating Data Tables by Using a
PivotTable, Multiple Consolidation Ranges
Another technique for consolidating data is using PivotTable, Multiple
Consolidation Ranges. For further information and an explanation of
the use of PivotTables for data analysis, see Chapter 22, PivotTable.
Use the examples that are presented here – salary tables using the
1. Select an empty sheet in the workbook. Select Cell A1.