Forum Discussion
Solved
Finding For Specific Orders
Hello All,
hope all is well, i am trying to find numbers (Orders) with specific criteria. For example, Orders with receipt numbers 001,002,003,004 and 005 have all entered various codes A,B,C or A,B,C,D or some the same combinations. I want to find the all the receipts that have made a Specific entry A only and weed out the rest.
thank you for your help!
=UNIQUE(FILTER('2022 Jan-Oct Data'!A:A,COUNTIFS('2022 Jan-Oct Data'!A:A,'2022 Jan-Oct Data'!A:A,'2022 Jan-Oct Data'!$AA:$AA,"<>"&"LOF")=0))
In your formula you refer to "LOF". According to your example you want "LOP".
Your formula refers to the whole columns A:A and AA:AA in sheet 2022 Jan-Oct Data. This is a reference to over 1 million rows and might result in an excessively long calculation time. Therefore i'd suggest to refer to e.g. 70000 rows if your data is over 50000 rows.
The 0 result means that there are 0 receipt numbers which only have code LOF. I applied this formula to a sample worksheet and it returned the expected result:
=UNIQUE(FILTER('2022 Jan-Oct Data'!A1:A70000;COUNTIFS('2022 Jan-Oct Data'!A1:A70000;'2022 Jan-Oct Data'!A1:A70000;'2022 Jan-Oct Data'!AA1:AA70000;"<>"&"LOP")=0))
=FILTER(C3:D7,ISNUMBER(SEARCH("A",D3:D7)))If you work with Excel 2019 or later you can apply FILTER.
- Thank you for your response, it partially worked... it filtered to the receipt that used A along with other codes of the same receipt but what i am looking for is to filter to the receipt that only has code A not A,B, or C
