Finding For Specific Orders

Occasional Contributor

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!

10 Replies



If you work with Excel 2019 or later you can apply FILTER.

receipt code.JPG


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



Is this what you want to do?


As you can see here there are duplicate entries with different codes 20p, unknown and LOP and so on. I want to find the entries on the left that only has LOP and nothing else. if the entry has something other than an LOP I don't want it filtered. if an entry have an LOP and 20P and Unknown i do not want it filtered.. if the entry has only LOP i want to find that entry.. Does that help?







You can apply this formula.

specific orders.JPG


when i plugged the formula into the cell it returned Zero, this is the formula i used
=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))


Am i doing something wrong i have over 50,000 rows? 

best response confirmed by Captain13 (Occasional Contributor)


=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))



            '2022 Jan-Oct Data'!$AA:$AA <> "LOP",
            '2022 Jan-Oct Data'!A:A

It shall be relatively fast.


thank you Sergei for your response, i do not have the TOCOL funtion in my excel, do you know how i can add it? 


if so, can you please elaborate further? 


Depends on your Excel version. That's Office 365, perhaps Current channel is enough now.