Comparing, merging, calculating colums of data in Excel
- by hickster
I would like to create a formula that a) compares four columns of data (see below)
Sep Oct
name units name units
apple 2 apple 3
pear 3 pear 7
orange 4 banana 6
banana 3 toffee 5
then b) merges the two "names" column into one column, dropping any duplicates but still retaining the two unit columns (for months Sep and Oct)
Sep Oct
name units units
apple 2 3
pear 3 7
orange 4 0
banana 3 6
toffee 0 6
then c) creates a third column that compares "Sep units" against "Oct units" and produces the total in the "difference" column
Sep Oct
name units units difference
apple 2 3 1
pear 3 7 4
orange 4 0 -4
banana 3 6 3
toffee 0 6 6