Find Duplicate Numbers in Column and remove if different value in 2nd column else leave

justmeok

New Member
Joined
Jul 27, 2011
Messages
42
Office Version
  1. 365
Platform
  1. Windows
This is a little hard to explain in the title subject line but I will try to provide a clear example below. I receive a csv file from our website and then I am writing a power query to extract the data required from this file. We only have two categories and any orders that are ONLY for the second category will to be kept and orders for the first category or a mix of both categories need to be removed completely. See example and under the heading action is the result I expect from the DAX formula.

CategoryOrder NumberAction Reqd
Category11460Remove row
Category11461Remove row
Category21461Remove row
Category21462Keep row
Category21463Keep row
Category21463Keep row
Category21463Keep row

<tbody>
</tbody>

Thanks everyone hoping this is possible and one of you gurus can help :)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
If I understand your question I would suggest creating a 2nd import of your source data, filtering out Category 2 items, then merging with your original query on the order number. If your merged query has a value in the new column against a Category 2 value that means there was a matching Category 1 order and you don't want the row. Then in the merged query you can filter out all Category 1 values and all not null rows in the new column.
 
Upvote 0
Wow macfuller thank you so much!!! This is such a simple solution and it worked brilliantly :) Nothing like a bit of lateral thinking - you have saved my sanity. :LOL: I really really appreciate your prompt reply and assistance.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top