Comparing List Values

[keneastman]

I'm building a DB that will contain up to about 100 lists. Each list will contian up to 2000 numeric values (they will each be stored in a single column on the spreadsheet). I then want to be able to identify how many of the numbers in list 1 are also in list 2...or how many numbers in list 83 are also in list 21...etc. Ideally, I would be able to identify up to 10 lists and the "formula" would tell me how many matches there are across lists. The result table might look something like this...

List 1 2 3 4 5 6
1 100 41 2 76 ...
2 41 100 43 31 ...
3
4
5
6

The numbers in the grid represent the number of matches that intersect both lists.

Ideas????

Thanks.

[ This Message was edited by: keneastman on 2002-04-18 18:28 ]

[Aladin Akyurek]

On 2002-04-18 18:27, keneastman wrote:
I'm building a DB that will contain up to about 100 lists. Each list will contian up to 2000 numeric values (they will each be stored in a single column on the spreadsheet). I then want to be able to identify how many of the numbers in list 1 are also in list 2...or how many numbers in list 83 are also in list 21...etc. Ideally, I would be able to identify up to 10 lists and the "formula" would tell me how many matches there are across lists. The result table might look something like this...

List 1 2 3 4 5 6
1 100 41 2 76 ...
2 41 100 43 31 ...
3
4
5
6

The numbers in the grid represent the number of matches that intersect both lists.

Ideas????

Thanks.

[ This Message was edited by: keneastman on 2002-04-18 18:28 ]

Select all cells of the first list, go to the Name box, type List1, and hit enter. Iterate this procedure until you have named all your lists.

Activate another worksheet.

In A2 enter: List1

and copy down until you have all list names in column A.

In B1 enter: List1

and copy across until you have all list names in row 1.

In B2 enter:

=SUMPRODUCT((COUNTIF(INDIRECT($A2),INDIRECT(B$1))))

Copy this across then down.

For data consisting of 3 lists, you'd get something like this:

{"","List1","List2","List3";
"List1",3,1,2;
"List2",1,3,1;
"List3",2,1,3}

[nisht]

Download the file nos 12 'compare range' from my website


http://www.pexcel.com/download.htm

hope this helps you.


nishith desai
http://www.pexcel.com