+ Reply to Thread
Results 1 to 6 of 6

counting duplicates within array formula?

  1. #1
    Registered User
    Join Date
    08-28-2006
    Posts
    7

    counting duplicates within array formula?

    range1-> 1 2 3 1 2 3
    range2-> a c a a b a

    How do I count duplicates within an array formula?

    Say I generated an array formula range2= "a"*range1 I would get an array
    {1,0,3,1,0,3}

    How do I write the formula to search within that array to count the number of entries that appear exactly twice and how high it is. (3 for the example above. Second highest is 1).

  2. #2
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Quote Originally Posted by etmac
    range1-> 1 2 3 1 2 3
    range2-> a c a a b a

    How do I count duplicates within an array formula?

    Say I generated an array formula range2= "a"*range1 I would get an array
    {1,0,3,1,0,3}

    How do I write the formula to search within that array to count the number of entries that appear exactly twice and how high it is. (3 for the example above. Second highest is 1).
    1. For counting those that appear exactly twice, try the following:

    =SUM(IF(COUNTIF($A$1:$F$1,A1:F1)=2,1,0))/2

    Entered with CTRL+SHIFT+ENTER.

    2. For counting the highest:

    (A) If you mean the highest of them all:

    =MAX($A$1:$F$1)

    (B) If you mean the highest of the ones that appear twice:

    =MAX(IF(COUNTIF($A$1:$F$1,A1:F1)=2,A1:F1,""))

    Entered with CTRL+SHIFT+ENTER.

    Scott

  3. #3
    Registered User
    Join Date
    08-28-2006
    Posts
    7
    Oh thank you for the reply. I've been stuck for days trying to figure this out.

    Yes the formulas that you wrote do what I was looking for except is it possible to write it so that it works on the array in "memory"

    so to continue my example...
    (range1 is a1:f1, range2 is a2:f2)
    (actual data on the worksheet)
    range1-> 1 2 3 1 2 3
    range2-> a c a a a a

    ...the array within which I'm looking duplicates for is
    {(a1:f2)*(a1:f2="a")}
    which would be {1,0,3,1,2,3}

    Do I have to have that array on the worksheet somewhere or can i work on it within "memory"? I tried just pasting that formula into the ones you provided but it didn't seem to work.
    i.e. (=SUM(IF(COUNTIF(($a$1:$f$2)*($a$1:$f$2="a"),(a1:f2)*(a1:f2="a"))=2,1,0))/2)

    Thanks for your help, really!
    Last edited by etmac; 08-31-2006 at 06:54 PM.

  4. #4
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    To do it all in memory will take someone other than me to figure out. I suppose you could write a custom function to do it in VBA.

    Scott

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Try

    =SUM(--(FREQUENCY(IF(A2:F2="a",A1:F1),IF(A2:F2="a",A1:F1))=2))

    confirmed with CTRL+SHIFT+ENTER
    Last edited by daddylonglegs; 09-01-2006 at 07:54 PM.

  6. #6
    Registered User
    Join Date
    08-28-2006
    Posts
    7
    thanks daddy for the reply. it seems like your formula provides the number of pairs found in the array. While this is useful I'm looking for the highest value of the "pairs". In the above example the highest pair would be 3. The second highest is 1.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1