Advanced Formula Challenge #6: Occurs once, non-contiguous range 9

The challenge this week is as follows: given a non-contiguous range, made up of an arbitrary number of single-column ranges, the values in each of which being either a numeric, text or null string, to generate a list, in numerical order and beginning in A2, of all numbers which occur precisely once within that range.

For example, in the below:

Occur Once Non-Contiguous Ranges

three numbers – 1, 2 and 9 – occur precisely once across the non-contiguous, union range defined by:

=C2:C7,E2:E4,E6:E9,G3:G11,J1:J9,L5:L6,N3:N7,N9:N10,N12

(You can download the file here.)

As usual, the formula in A2, when copied down, is to produce a blank in cells beyond the expected number of returns. What’s more, this is to be done via reference to another formula in A1, also to be derived, and whose value is equal to that expected number of returns. In this example that value is 3.

As such, the formula in A2 is to begin with:

IF(ROWS($1:1)>$A$1,"",

Also note that, even though the other cells in the range C1:N12 are empty in this example, this cannot be assumed to always be the case. In fact, solvers should assume that there may be numericals within those other cells as well.

Solution next week. Best of luck!

9 comments

  1. For A1:

    =SUMPRODUCT(--(FREQUENCY(($C$2:$C$7,$E$2:$E$4,$E$6:$E$9,$J$1:$J$9,$L$5:$L$6,$N$3:$N$7,$N$9:$N$10,$N$12),ROW(1:10)-1)=1))

    A2 and copy down:

    =IF(ROWS($A$2:$A2)>$A$1,"",SMALL(IF(FREQUENCY(($C$2:$C$7,$E$2:$E$4,$E$6:$E$9,$J$1:$J$9,$L$5:$L$6,$N$3:$N$7,$N$9:$N$10,$N$12),ROW($1:$10)-1)=1,ROW($1:$11)-1),ROWS($A$2:$A2)))

    Or

    A1:

    =SUMPRODUCT(--(FREQUENCY($C$1:$N$12,ROW(1:10)-1)=1))

    A2:

    =IF(ROWS($A$2:$A2)>$A$1,"",SMALL(IF(FREQUENCY(($C$1:$N$12),ROW($1:$10)-1)=1,ROW($1:$11)-1),ROWS($A$2:$A2)))

    Maybe I misunderstood your requirements?

    Regards

  2. Apologies – my fault for not being so clear.

    Your second solution is not viable, as I should’ve mentioned that the other cells in C1:N12 could contain numericals which are not to be returned (otherwise it would kind of defeat the point of the non-contiguous range bit, wouldn’t it!).

    And I completely forgot to mention that the returns should be in numerical order.

    I will add both to the thread now.

    Also, you may want to store that union range as a Defined Name?

    But no, in general I don’t think you have misunderstood my requirements.

  3. named range ‘Range_NC’ is

    =$C$4:$C$7,$E$4:$E$9,$G$5:$G$10,$J$1:$J$9,$L$5:$L$6,$N$4:$N$12

    then count is

    =COUNT(1/(FREQUENCY(Range_NC,Range_NC)=1))
  4. Very good, Cyril, and precisely what I used, though of course there are several alternatives available, such as the SUMPRODUCT version used by Bill, which is also perfectly good (although the ROW(1:10) construction is perhaps a touch “static”).

    That’s the count. What about the main formula?

  5. Hi XOR LX!

    Create a Defined Name RNG with the data.

    For the count:

    =SUMPRODUCT(--(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1))

    And the Extract Formula in A2 (Confirm CSE):

    =IF(ROWS(A$2:A2)>$A$1,"",SMALL(IF(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1,ROW(INDIRECT("1:"&MAX(RNG)+1))-1),ROWS(A$2:A2)))

    Another Option Can be (without CSE):

    =IF(ROWS(A$2:A2)>$A$1,"",AGGREGATE(15,6,(ROW(INDIRECT("1:"&MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1),ROWS(A$2:A2)))

    ¡Blessings!

  6. Good to see you again, John!

    And I can’t fault that solution whatsoever. Great stuff and well done!

    (Nice of you to throw in a non-array version as well. I always wonder at which point over the next few years we’re going to start assuming as default that the majority of users have Excel 2010, and so we can start including the under-employed AGGREGATE in more of our solutions.)

    Thanks a lot.

  7. Nothing much to add.

    use Named range Val_cnt described numerous times in your other challenges:

    =ROW(INDIRECT("1:"&COUNT(Range_NC)))

    And in A2, CSE:

    =IF(ROWS($1:1)>$A$1,"",SMALL(IF(FREQUENCY(Range_NC,SMALL(Range_NC,Val_cnt))=1,SMALL(Range_NC,Val_cnt)),ROWS($1:1)))

    PS: Using Excel for MAC so NOT everyone is using Excel 2010… 🙂

    Now what interested me was to return as well the text part AND the integers but in ascending order.

  8. Great stuff, Cyril. A slightly different approach towards constructing the bins_array, but equally good.

    This demonstrates the flexibility we have here with regards to this parameter. Although in this particular case we don’t actually need to define it so precisely (In fact, Bill’s ROW(1:10)-1 will work just fine, although we should probably put an INDIRECT in to make it immune to row insertions), it’s nevertheless good practice to do so, as we may find ourselves in situations where such rigour is in fact required.

    Sorry – I should’ve subtitled this site (Non-Mac) Advanced Excel Techniques… 🙂

    Interesting other query as well. What “order” exists for numerics and text, though? Is “a” before 1?

  9. 1- “Non -MAC”… Alas I shall remain a lone macexceler… hehehe
    2- Maybe text first then integers although the other way around is not an issue…
    so either:
    y
    x
    w
    s
    m
    l
    e
    d
    9
    2
    1
    or
    1
    2
    9
    d
    e
    l
    m
    s
    w
    x
    y
    Now, thinking of it, maybe the second would make more sense?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s