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.
Solved! Go to Solution.
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.
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
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.
That worked, thanks @NickSm !