Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (97)

    Thanks for the answer. It works for what I'm trying to do!

    Is there any function to compare two arrays (say of same dimension) and call out the number of matches?

    For instance I have two lists of ten numbers each and I want to know how many of the entries are in both lists?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: excel (97)

    This will yield TRUE if the value in cell A1 occurs in the array from B1:B25. If it does NOT it returns FALSE. Change as appropriate.
    =isnumber(Match(a1,B1:B25,0))

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (97)

    Are the two lists in the same order so that matching values are in coresponding cells? If so, and if the values are in A1:A12 and B1:B12, then the following array formula (hold down Shift+Ctrl when you press the Enter key to enter the formula) will do that:

    <pre>=SUM((A1:A12=B1:B12)*1)
    </pre>

    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: excel (97)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Oct-03 15:08. Add PS)</P>Does this array formula do what you want? (confirm with ctrl-shift-enter).

    =SUM(IF(COUNTIF(A1:A12,C1:C12)>0,1))

    Change ranges as appropriate.

    I assume there are NO dupes in array C. If there are you will get multiple hits, even if there is one element of it in array A. For example if a1: A12 is numbers 1-2 and c1:c12 is all 1's it will find 12 matches since it each 1 is in the list. If there are some constraints of this sort, what are they?

    Steve

    Check out Chip Pearson's site for other info on duplicates and unique items in ranges.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (97)

    Neither array has repeating elements.

    The formula you gave me does the trick!

    Pardon my ignorance, but that's the 1st time I ever did a "ctrl-shift-enter" to enter an expression. What does it do? (I see that curly brackets do result.)

    Thanks.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (97)

    No such luck, but I'm thinking I ought ot be able to find a use for your formula now that I have it.

    I'll keep it in my excel "folder".

    Thanks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: excel (97)

    It is for entering ARRAY formulas.

    For a good Intro on array formulas, see Chip Pearson's Site

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching values in a range (97)

    Subject of this thread edited by HansV to make it more descriptive

    I'm looking for an excel function to report whether a given cell value occurs in an array. Any help would be appreciated...

Posting Permissions

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