Duplicates rows but pairings match across different colums

[PeterAP]

Hi,

I'm looking at balances between companies comparing what is held in one company's books with another, and then vice versa (I had to do a self join to do this as it is all in one data source). This has resulted in duplicates (as expected due to the self join). I need to strip out these duplicates, but as you can see the criteria for identifying duplicates is in the opposite fields.

For example the Company 1 / Company 2 result is the same as the Company 2 / Company 1 result. I only need one of these - How can I strip the second pair out? I've tried several ways but can't seem to strip out the duplicates.

Company Name	AF Name	Company USD	AF USD	Difference
Company 1	Company 2	30,172,187.85	- 30,104,689.36	60,276,877.21
Company 3	Company 4	4,423,366.84	-    4,347,685.16	8,771,051.99
Company 4	Company 3	-    4,347,685.16	4,423,366.84	8,771,051.99
Company 5	Company 6	50,772.81	-          89,725.92	140,498.73
Company 2	Company 1	- 30,104,689.36	30,172,187.85	60,276,877.21
Company 6	Company 5	-          89,725.92	50,772.81	140,498.73

[Luke_C · Accepted solution]

Hi @PeterAP 

Here's one way:

1. Add a record ID and transpose the fields with the company names
2. Sort the names alphabetically
3. Concatenate the names
4. join the concatenations back to the data based on record ID
5. Remove dupes, the first instance is kept

[gabrielvilella]

Well, you can just use the Unique on the difference column. 

[DataNath]

@gabrielvilella although they're large numbers, it can't really be left to chance that they'll always be unique. On the off chance that there are multiple sets of companies with the same difference, Alteryx will drop all but the first combination. The solution provided by @Luke_C is fully dynamic and will ensure true uniqueness.

[gabrielvilella]

You can just round the numbers first then use the unique.

[PeterAP]

Thanks @Luke_C , this solution really worked for me!