Lookup and replace
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That worked, thanks @NickSm !