Need help showing rows that DO NOT contain a certain term

[dsabot]

I have a spreadsheet that has two columns, OrderNumber and SKU.

I am trying to find OrderNumbers that DO NOT contain a specific SKU in them.

For example:

OrderNumber SKU
1018 BCM-USA
1018 BBC-12-PP
1018 SB-WG
1019 BCM-FL

As you can see, the OrderNumber is repeated numerous times if the customer ordered more than 1 SKU.

In the above example, I would NOT want to see ANY of the OrderNumber 1018's because there is an instance of SKU BBC-12-PP so I would want to filter that out...

[JohnTopley]

Only one SKU or a selection of SKUs to be matched?

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dsabot]

Unfortunately, I am unable to attach a sample workbook, I am sorry, not sure why it won't work.

Basically, I am trying to take this:
OrderNumber SKU
1018 BCM-USA
1018 BBC-12-PP
1018 SB-WG
1019 BCM-FL

And turn it into this:
OrderNumber SKU
1019 BCM-FL

ALL instances of OrderNumber 1018 are removed as it contains SKU BBC-12-PP

[sandy666]

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[dsabot]

I understand how to attach a file, however the interface isn't working. No dialogue box is appearing. I have tried Chrome and IE to no avail.

[sandy666]

Maybe try right click on Manage Attachments and choose: Open link in new tab
and turn off block pop-up window (or AdBlock, NoScript... etc...)

[dsabot]

I tried that. Tried incognito. Tried on another computer. I am literally unable to either attach an image OR attach a file. I can provide a link to a file on dropbox if that helps?

[sandy666]

Probably yes

[dsabot]

docs.google.com/spreadsheets/d/1fgM44j6lO_xooogyfMI2wiD3IY01Mt0k_xR5efpFBIQ/edit?usp=sharing

Here is the spreadsheet with one tab showing the data we have, and the other showsing how we want it to look.

[sandy666]

.
dsabot's file

[JohnTopley]

Helper in C "Data we have"

in C2

=IF(COUNTIFS(A:A,A2,B:B,"BCM-USA"),"",1)

in A2 of "Output"

=IFERROR(INDEX('Date we have'!A2:A24,SMALL(IF('Date we have'!$C$2:$C$24=1,ROW('Date we have'!$A$2:$A$24)-ROW($A$2)+1,""),ROWS(A2:A2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across and down