Comparison list

[sigfreid]

Hi

Hopefully someone can answer this (seemingly simple) query

If I have 2 lists, for example

1 1
2 3
3 4
4 5
7 6
8 7
9 10

How would I output a 3rd list (or pair of lists) that showed the differences and matches, for example 1 list like

1
3
4
7

To show they both have those values or 2 lists like this

1 1
2 -
3 3
4 4
- 5
- 6
7 7
8 -
9 -
- 10

Thanks

[NBVC]

Say your range is in A2:B8, then in C2:

=IF(COUNTIF($B$2:$B$8,A2)>0,COUNT(C$1:C1)+1,"")

copied down

in a new column:

=IF(ROWS($A$1:$A1)>MAX($C$2:$C$8),"",INDEX($A$2:$A$8,MATCH(ROWS($A$1:$A1),$C$2:$C$8,0)))

copied down as far as you need.

Note: If you have a large database, store =MAX($C$2:$C$8) adjusted, in a single cell, and then replace that reference in the formula above so that it doesn't do that calculation over and over...

[Marcol]

This might be of some help

It gives the result in three columns

1/. Match A&B
2/. In A & not B
3/. In B & Not A

Cheers