Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Comparing List Values

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Ken Eastman
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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}

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


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

    hope this helps you.


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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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