topic Re: Easiest way to make two columns into a match formula? in Discussions

Easiest way to make two columns into a match formula?

miguello — Tue, 25 Jun 2019 19:33:46 GMT

All,

Simple task, but on a great scale.

I have a table with two columns that have two different naming conventions for the same thing. Few hundreds of rows. Let's say something like that:

NATO reporting name Common name

Faceplate	Mikoyan-Gurevich Ye-2A
bleepot	Mikoyan-Gurevich MiG-15
Fang	Lavochkin La-11
Fantail	Lavochkin La-15
Fantan	Nanchang Q-5/A-5
Fargo	Mikoyan-Gurevich MiG-9
Farmer	Shenyang J-6 and Mikoyan-Gurevich MiG-19 [1]
Feather	Yakovlev Yak-15/Yak-17
Fencer	Sukhoi Su-24
Fiddler	Tupolev Tu-28/Tu-128
Fin	Lavochkin La-7
Finback	Shenyang J-8
Firebar	Yakovlev Yak-28P
Firebird	Chengdu J-10
Fire Fang	Chengdu J-20
Firkin	Sukhoi Su-47
Fishbed	Mikoyan-Gurevich MiG-21
Fishcan	Chengdu J-7 [2]
Fishpot	Sukhoi Su-9 and Su-11

and so on. Now I need an easy way of creating a Match formula out of these columns. Any suggestion rather than parsing the table externally and adding pieces of formula around it?

The reason I need a formula, and not a Virtual Join or something is that I have a column that have mixed names, some are Nato reporting name some are common names. Formula would work on Nato names, changing it to Common Names, leaving Common Names untouched.

Also, any other way of reducing two naming conventions in one column to one if I have a table of matched names from both conventions?

Thanks!

Re: Easiest way to make two columns into a match formula?

Jeff_Perkinson — Tue, 25 Jun 2019 21:42:53 GMT

It's pretty easy to manipulate the columns of the table into the bones of the Match() codes that you need by concatenating the two columns along with some double quotes (") and commas (,) with a formula like this:

"\!"" || :a || "\!" , \!"" || :b || "\!", "

Then, just highlight the new column with the concatenated values in the data table and choose Edit->Copy to get those values onto the clipboard.

Now, you can open your new Formula Editor where you want the Match(), add the Match function, set the match column appropriately and then double click on the Match function to get the JSL editor for it. Paste over the two Empty() functions with your copied values. Be sure to remove the trailing comma (,).

Re: Easiest way to make two columns into a match formula?

miguello — Tue, 25 Jun 2019 22:42:40 GMT

Awesome, thanks! Exactly what I had in mind, for some reason got stuck on constructing formula code by external means when I could easily do that in the table itself.