Thanks:  0
Likes:  0

1. 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 ]

2. 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}

hope this helps you.

ni****h desai
http://www.pexcel.com

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•