Deduplicate data which inverted further down the list

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

Workflow

Regex

Tips and Tricks

Parse

Macros

Accepted answers

kgalbert

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

FX Rates.xlsx

FX Rates.yxmd

All comments

kgalbert

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

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

Deduplicate data.yxmd

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

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

FX Rates.yxzp

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335