Lookup and replace

[vvissamsetty]

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.

[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

[NickSm · Accepted solution]

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.

[vvissamsetty]

That worked, thanks @NickSm !