SOLVED

Finding For Specific Orders

Copper 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

@Captain13 

=FILTER(C3:D7,ISNUMBER(SEARCH("A",D3:D7)))

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

@Captain13 

=FILTER(C3:C10;D3:D10="A")

Is this what you want to do?

filter.JPG 

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?

Captain13_1-1668703344862.png

 

 

@OliverScheurich 

@Captain13 

=UNIQUE(FILTER(A2:A19,COUNTIFS(A2:A19,A2:A19,$B$2:$B$19,"<>"&"LOP")=0))

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 (Copper Contributor)
Solution

@Captain13 

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

@Captain13 

Alternatively

=UNIQUE(
    TOCOL(
        IF(
            '2022 Jan-Oct Data'!$AA:$AA <> "LOP",
            NA(),
            '2022 Jan-Oct Data'!A:A
        ),
        3
    )
)

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? 

@Captain13 

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

1 best response

Accepted Solutions
best response confirmed by Captain13 (Copper Contributor)
Solution

@Captain13 

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

View solution in original post