+ Reply to Thread
Results 1 to 2 of 2

Return a unique list from two columns based on a criteria

  1. #1
    Forum Contributor
    Join Date
    11-07-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    104

    Return a unique list from two columns based on a criteria

    Hi all,

    I am looking for a formula that will return a unique list from two columns based on if it meets the criteria from another column


    Table 1

    Department 1------Sub Dept 1
    Department 1------Sub Dept 1
    Department 1------Sub Dept 2
    Department 1------Sub Dept 3
    Department 1------Sub Dept 5
    Department 2------Sub Dept 1
    Department 2------Sub Dept 2

    Table 2
    Department 1------Sub Dept 1
    Department 1------Sub Dept 2
    Department 1------Sub Dept 3
    Department 1------Sub Dept 4
    Department 2------Sub Dept 1
    Department 2------Sub Dept 2


    Return a unique sub department list for Department 1

    Unique List

    Sub Dept 1
    Sub Dept 2
    Sub Dept 3
    Sub Dept 4
    Sub Dept 5

    Hope that makes sense, I know how to get the unique lists with criteria but, am struggling to get it to look at two tables and return one results.

    Any help would be very much appriciated.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    6,927

    Re: Return a unique list from two columns based on a criteria

    Given that there are only two "tables" this is manageable using this proposed method.
    The 'helper' column, which may be moved and/or hidden for aesthetic purposes, displays all sub dept's in department 1 using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The 'final' column displays sorted unique values using*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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