Jeffs problem No. 14273 for experts to array formulas
Posted by Eli Weiss on January 13, 2002 9:59 PM
I am interested too in thease kind of problems
It could be solved by macros of course, but I am sure that array formulas using =countif etc. can solve this problem simply
There was no answer to this question yet
Can anybody try to solve it?
Jeffs question from 1/9/02 was as follows:
I'm looking for a way to compare two seperate lists of numbers and having Excel create a third list that shows only non-duplicated entries. In other words, I want List 3 to consist of the numbers that appear in List 1, but that are not in List 2. Example below:
List 1 List 2 List 3
------ ------ ------
1 1 2
2 3 4
3
4
Thanks for your help!!
Posted by Aladin Akyurek on January 14, 2002 12:15 AM
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
Posted by Eli Weiss on January 14, 2002 12:28 AM
Wow!! Aladin. Much appreciation.
Aladin
Thank you again,
Eli
