Forum Discussion
Finding For Specific Orders
=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))
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?
Depends on your Excel version. That's Office 365, perhaps Current channel is enough now.