+ Reply to Thread
Results 1 to 2 of 2

Extract unique values out of list based on an extra criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    2

    Extract unique values out of list based on an extra criteria

    Hello,

    I have been searching and trying many examples to get an list of unique values out of a dataset. Attached you'll find the worksheet with the data and what the expected output should be.

    I managed to extract unique values without the additional criteria using the following array formula:
    ={INDEX(tbl_AR[ID];MATCH(0;COUNTIF($A$5:A5;tbl_AR[ID]);0))}
    The results are all the unique values of the dataset, in this case tbl_AR (around 8800 rows).

    The problem:
    I don't manage to find the correct formula whereby only the unique values are extracted when administration = [parameter]. In this example 6003.
    I tried to work with function rows but then i get the error that formula is too long


    Any tips and / or guidance would be highly appreciated.

    Thx in advance,

    Sven
    Attached Files Attached Files
    Last edited by sven1975; 10-11-2012 at 05:05 AM.

  2. #2
    Registered User
    Join Date
    10-10-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Extract unique values out of list based on an extra criteria

    I have found a solution using a dynamic range where the range is determined using the parameter. For those interested in the formula;

    The dynamic range:
    ={OFFSET(tbl_data_AR!$A$1;MATCH($B$1;tbl_AR[administration];0);0;MATCH($B$1;tbl_AR[administration];1)-MATCH($B$1;tbl_AR[administration];0);1)}
    In the range name manager i used then the name "range_admin"

    The formula to get the unique values out of the dynamic range becomes then:
    ={IFERROR(INDEX(range_admin;MATCH(0;COUNTIF($C$5:C5;range_admin);0));"")}
    Thx,

    Sven
    Attached Files Attached Files

+ 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