Hi Eli,

I didn't check Jeff's query. I'll take up here the query as you have formulated, that's, creating a difference list: List1-List2 = List3. Moreover, I'll do it in a single sheet, although that doesn't matter in essence.

Lets say that A1:B5 houses the sample data:

{"List1","List2";

1,1;

2,3;

3,"";

4,""}

where "" stands for blank cells.

C1 houses the label "List3".

In D2 enter: =MATCH(9.99999999999999E+307,A:A)

This gives us the row number of the last cell of List1. This computation exploits the fact that List1 is of numeric type. Otherwise, a different formula is needed to determine the row number of the last cell of List1.

In D3 enter: ="$C$2:"&ADDRESS(D2,3)

Enter the following labels

{"Diff Set","Loc Set","Sorted Set"}

in E1:G1.

In E2 enter: =IF(COUNTIF(B:B,A2),0,A2)

This formula already produces the desired List3 (here the relevant items are interspersed with 0's, which could be formula returned blanks instead).

Copy down the formula in E2 till the last row of List1.

In F2 enter: =SUMPRODUCT((E2>$E$2:$E$5)+0)+1

which computes the position/location of each item in Diff Set in order to sort Diff Set..

Copy down the formula in F2 till the last row of List1.

In G2 enter: =IF(ISNUMBER(MATCH(ROW()-ROW($F$2)+1,$F$2:$F$5,0)),INDEX($E$2:$E$5,MATCH(ROW()-ROW($F$2)+1,$F$2:$F$5,0)),0)

fetches the items from the Diff Set according to their postion.

Copy down the formula in G2 till the last row of List1.

In C2 array-enter: =IF(ROW()-ROW(INDIRECT($D$3))+1>ROWS($G$2:$G$5)-COUNTIF($G$2:$G$5,0),"",INDIRECT(ADDRESS(SMALL((IF($G$2:$G$5<>0,ROW($G$2:$G$5),ROW()+ROWS($G$2:$G$5))),ROW()-ROW(INDIRECT($D$3))+1),COLUMN($G$2:$G$5))))

finally eliminates 0's to produce List3 which is equiv to Diff Set.

Copy down the formula in C2 till the last row of List1.

Note that the array-formula is really needed to get a sorted list without 0's (or blanks) the essential formula in E has created.

In case it's needed: To array-enter a formula, hit control+shift+enter at the same time, not just enter.

Aladin

Wow!! Aladin. Much appreciation.

Aladin

Thank you again,

Eli