Help with formula around linked order numbers

[MXMaster]

Hi everyone,

Really struggling with this one and even Chat GPT failed me...
I'm also no excel expert but gave it go.....

I'm just looking for the right formula that can return a simple TRUE / FALSE in cells to right.


Objective:
I have a list of customer orders (col A), and the letters in (col N) associate to specific add-ons my team have also sold in the same order - 'O'.
L - This is the main product itself so is the primary product
O - This is the accessory sold with 'L' the primary product (They attached and sold an add-on - well done!)


The Help:
What formula (or best way) would mine all this long data list of orders and show me clearly - which primary products L, also has a secondary add-on on same order 'O'?
BUT - I want the formula to exclude order numbers which only have 'O' alone in them, as this is just an accessory and not a primary product sale?

Any support welcome on this one.
I tried to show how far I got and it only every return the word 'FALSE' back.

Thanks in advance!

 

[Fluff]

How about

=AND(N2="L",COUNTIFS(A:A,A2,N:N,"O"))

Or do you just want a separate list of the orders with both L & O

 

[MXMaster]

How about

=AND(N2="L",COUNTIFS(A:A,A2,N:N,"O"))

Or do you just want a separate list of the orders with both L & O

Hi there,

I was thinking, is there a way to list them maybe on another tab without all the duplicates of order numbers showing?
Was think some sort of smart pivot table - but I'm basic level but can do these?

Or anything you would suggest to make this real easy to view in a list - I could then even work out attachment % if that's the case of accessories in orders!

 

[Fluff]

What is the last row of your data?

 

[MXMaster]

Hi there,

Line: 273624

Amazing support - thanks!

 

[Fluff]

You could try

=UNIQUE(FILTER(A2:A300000,BYROW(A2:A300000,LAMBDA(br,SUM(SIGN(COUNTIFS(A:A,br,N:N,{"l","O"})))))=2))

But it will be slow for that amount of data.
Power query might be a better option, but don't know it can be done that way.

 

[MXMaster]

This is amazing - thank you so much - worked a treat! genius!

 

[Fluff]

You're welcome & thanks for the feedback.