How to return a list of distinct items using Sort & Unique based on 2 conditions

[deacon10]

Hi all you wonderful people,

I am trying to return a list of distinct items using the sort formula below.

=SORT(UNIQUE(FILTER(sheet2!$P$2:$P$300,sheet2!$N$2:$N$300="Test1")))

This works perfectly, except I only want to return items where sheet 2 column N = “Test1” or “test2”.

I am guessing that I must be missing something super easy. Do any of you wonderful people have any ideas?

 

[Mposwal]

Use this:
=SORT(UNIQUE(FILTER(Sheet2!$P$2:$P$300,(Sheet2!$N$2:$N$300="Test1")+(Sheet2!$N$2:$N$300="Test2"))))

 

[deacon10]

Use this:
=SORT(UNIQUE(FILTER(Sheet2!$P$2:$P$300,(Sheet2!$N$2:$N$300="Test1")+(Sheet2!$N$2:$N$300="Test2"))))

Hi there, thanks for this,

This does something very odd. It doesn’t return all from “test1”, some from “test3” (“test3” not specified in formula), and nothing from “test2”. It also returns a #N/A and some references that are not within column P.

 

[Fluff]

That formula should not do that, can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Mposwal]

Please share the data as it is working well from my (less) data:

 

[deacon10]

Thanks all,

Mposwal you sorted it!

Use this:
=SORT(UNIQUE(FILTER(Sheet2!$P$2:$P$300,(Sheet2!$N$2:$N$300="Test1")+(Sheet2!$N$2:$N$300="Test2"))))

I missed the open bracket from "test1" in your correct formula above.

My incorrect formula is as below
=SORT(UNIQUE(FILTER(Sheet2!$P$2:$P$300,Sheet2!$N$2:$N$300="Test1")+(Sheet2!$N$2:$N$300="Test2"))))