+ Reply to Thread
Results 1 to 5 of 5

add to array formula to only return unique values

  1. #1
    Registered User
    Join Date
    06-09-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    add to array formula to only return unique values

    All,

    I have the following array formula and would like to add something so it only returns a unique value (no duplicates). I can create this array then use a COUNTIF array to relist, however the reference value i'm using to determine the number of cells to perform this action with only counts unique values so the resultant "reference" value is lower than the number of cells this forumla would need to run in to capture all values.

    Array:
    =IFERROR(IF(ROWS(C$20:C20)<='INFO Sheet'!E$9,INDEX('UNI Delivery Report 6-6'!B2:P10000,SMALL(IF('UNI Delivery Report 6-6'!B2:B10000=C$15,ROW('UNI Delivery Report 6-6'!B2:B10000)-ROW('UNI Delivery Report 6-6'!B2)+1,""),ROWS(C$20:C20)),15),""),"")

    Problem:
    INFO Sheet e9=2
    Column 15 has my returned value, but contains duplicates and only the first 2 occurences that meet the critera are returned eliminating additional entries. The "INFO Sheet E9" cell counts the number of unique values so I need the list to also only contain unique values.

    Any ideas??? Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: add to array formula to only return unique values

    Care to post the formula you apparently have in E9 on INFO Sheet?

  3. #3
    Registered User
    Join Date
    06-09-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: add to array formula to only return unique values

    Yes.

    Cell E9 is the second of a 3 part "process". The first formula is a simple vlookup that returns a value:

    This is in cells E30:E41:
    =IFERROR(VLOOKUP(VALUE(D30),'UNI Delivery Report 6-6'!A:L,10,0),"")

    From there, cells C9:C13 have each unique value returned and quantify the number of occurrences the values returned in E30 to E41:

    Cell E9 (arrayed to E13):
    =IF(C9<>"",SUM(IF(FREQUENCY(IF('INFO Sheet'!$E$30:$E$41=C9,MATCH('INFO Sheet'!$B$30:$B$41,'INFO Sheet'!$B$30:$B$41,0)),ROW('INFO Sheet'!$B$30:$B$41)-ROW('INFO Sheet'!B$30)+1),1),IF('INFO Sheet'!$C$68=B9,1,0)),"")

  4. #4
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: add to array formula to only return unique values

    Would you upload the file if possible?

  5. #5
    Registered User
    Join Date
    06-09-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: add to array formula to only return unique values

    The book is 93 meg, so uploading isn't a good option....

    I think I have found a workaround by sorting the indexed sheet using a column with unique values that ascend, the array then only returns the first (x) occurrences which are also unique since the duplicates have been moved down. I believe this will work since all of the entries have 2 duplicates with each one tied to a "value-1" and the second duplicate tied to a "value-2".

+ 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