Return a unique list from two columns based on a criteria

[elleb]

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.

[JeteMc]

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:

=IFERROR(INDEX(B$1:B$10,AGGREGATE(15,6,ROW($1:$10)/(A$1:A$10=A$1),ROW(1:1))),INDEX(E$1:E$10,AGGREGATE(15,6,ROW($1:$10)/(D$1:D$10=A$1),ROW(1:1)-COUNTIFS(A$1:A$10,A$1))))

The 'final' column displays sorted unique values using*:

=IFERROR(INDEX(I$2:I$20,MATCH(SUM(COUNTIF(J$1:J1,I$2:I$20)),COUNTIF(I$2:I$20,"<"&I$2:I$20),0)),"")

*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.