Identify duplicates based on other columns

[gmalpani]

Hello Experts,

I have a data with with details about the sales opportunities with 4 columns - Opportunity ID, Scenario ID, Product ID, Feature title.

For one opportunity (e.g. ABC123XYZ), multiple scenario can be created (e.g. ABC123XYZ-01, ABC123XYZ-02....). These scenarios may have same or different products with associated features. Note that original data has many more opportunities with combination of scenario, product & features. check attachment.
I need to find list of unique features under each opportunity ID for each product offered in an opportunity.
As shown in table below, last columns is expected output while first 4 are input data.
All these rows has same opportunity ID but has two scenarios and each scenario has two products.
For Opportunity + Product combination, I want to identify duplicates.

Input	Input	Input	Input	Expected output
Opportunity ID	Scenario	Product	Feature Title (text field)	Duplicate/Unique
ABC123XYZ	ABC123XYZ.01-01	Product 1	Feature 1 - this feature..	Unique
ABC123XYZ	ABC123XYZ.01-01	Product 1	Feature 2	Unique
ABC123XYZ	ABC123XYZ.01-01	Product 1	Feature 3	Unique
ABC123XYZ	ABC123XYZ.01-01	Product 1	Feature 4	Unique
ABC123XYZ	ABC123XYZ.01-01	Product 1	Feature 5	Unique
ABC123XYZ	ABC123XYZ.01-02	Product 1	Feature 1 - this feature..	Duplicate
ABC123XYZ	ABC123XYZ.01-02	Product 1	Feature 2	Duplicate
ABC123XYZ	ABC123XYZ.01-02	Product 1	Feature 3	Duplicate
ABC123XYZ	ABC123XYZ.01-02	Product 1	Feature 6	Unique
ABC123XYZ	ABC123XYZ.01-01	Product 2	Feature 10	Unique
ABC123XYZ	ABC123XYZ.01-02	Product 2	Feature 10	Duplicate
ABC123XYZ	ABC123XYZ.01-02	Product 2	Feature 11	Unique
ABC456XYZ	ABC456XYZ.01-01	Product 1	Feature 1	Unique
ABC456XYZ	ABC456XYZ.01-01	Product 1	Feature 2	Unique
ABC456XYZ	ABC456XYZ.01-02	Product 1	Feature 1	Duplicate
ABC456XYZ	ABC456XYZ.01-02	Product 1	Feature 3	Unique
ABC456XYZ	ABC456XYZ.01-03	Product 1	Feature 1	Duplicate
ABC456XYZ	ABC456XYZ.01-03	Product 1	Feature 2	Duplicate
ABC456XYZ	ABC456XYZ.01-03	Product 1	Feature 3	Duplicate
ABC456XYZ	ABC456XYZ.01-03	Product 1	Feature 4	Unique

 

[gmalpani]

Hi,

I managed to have required output on my own. So, consider it solved, not sure how can I mark this thread as 'Solved ?

Steps followed -
1. In original query (Query 1) add one index column starting from 1
2. Copy of original query (Query 2)
2. In Query 2, with Ctrl + Select of Opp ID, Product and Feature -> right click to remove duplicates. Now all features are unique in this query
3. Add one more custom columns 'Custom' with value = "Unique"
4. Merge original query with Query 2 with common column as Index
5. Open only column named 'Custom' in merged query
6. There will be records with value = "Unique" and null
7. Replace all null with "Duplicates"

Thanks again to all experts here.