Lookup and replace

Hi Guys,

I have thousands of lines of data with some duplicate security names and blank client instruments. I'm trying to create a step where if there are duplicate security names with blank client instrument, lookup the security name in the column and choose client instrument which is not blank. Basically I wish to do a vlookup on security name and populate blank client security id based on the name. Can anyone guide me on this?

Thanks.

replace.PNG

Tips and Tricks

Accepted answers

NickSm

Hey @vvissamsetty

My thoughts here are to essentially use a copy of your input as a bit of a self-lookup table, so that a join can append the relevant Client Instrument. Could also add a recordID if needed for sorting back to the original order after the join.

SelfLookup.yxmd

All comments

Loic

Here is what I would do: sort by first column and then second column. so the real values are first and null second in the second column. Then use "Find and replace" (it does a VLOOKUP too, like Join tool and others) and use the option "first instance" for the second column. it will return only the first non null value.

vvissamsetty

Hi @Loic , thanks for your reply. Apologies, I wasn't clear in describing my issues. I basically want the null values to be filled with client security ID based on the security name. In this instance I want the null values to be replaced with 'GOOG' which is found based on the common security name.

Thanks

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703