Deduplicate data which inverted further down the list

[KMiller]

Hi all

I am really stuck with this just don't seem to be able to figure it out. I have a list of exchange rates that need to be imported into our system. The problem is each exchange rate combination appears further down the list but it is inverted. The system will not accept the import unless these "duplicates" are removed. I have provided below a small example using the GBP, USD and EUR currencies and highlighted each pair of correct and inverted instance. Does anyone know how I could remove each inverted instance.

Thanks

Kevin

[kgalbert · Accepted solution]

Hi Kevin,

I would use the formula tool to create a new field that just combines the Base CCY and the Exchange CCY in alphabetical order.  Then use the Unique tool on that new field. 

I attached a workflow with this solution.  This assumes that the system you're uploading to only needs 1 exchange rate per currency pair and 'does the math' to go back and forth with this one rate.

Thanks,
Ken

[MarqueeCrew]

@KMiller,

This is almost a weekly challenge @JoeM and @kat and @NicoleJohnson should have been on top of this one.  I used an Anagram technique (Challenge #59) to alphabetize the contents of the Market Entity Code field.  Now EUR/GBP and GBP/EUR both appear as:  /BEGPRU.

Now you can use your unique process to find the duplicates.

Cheers,

Mark

[KMiller]

Thank-you @kgalbert and @MarqueeCrew for the solutions, can't believe how simple this was in the end, my failed attempts were making this far too complicated.

Cheers

Kevin

[kgalbert]

Hey @MarqueeCrew,

I think your solution would give incorrect answers because some countries share the same 3 letters in their currency codes.  For example USD/SAR (Saudi Arabia Riyal) and USD/ARS (Argentina Peso) would both dedup in your process to /ADRSSU. 

The three letter currency codes need to stay as blocks.

Thanks,
Ken

[MarqueeCrew]

Good Feedback!

Thanks

[kat]

@MarqueeCrew, I've actually had this exact problem at work and did remember a weekly challenge. I thought more of Challenge #88 though, which preserves the full word, anagram style. That would be something like this:

But then there's @kgalbert's 2 tool solution which makes mine feel unnecessarily long! 

Cheers

Kat