Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level V

## Easiest way to make two columns into a match formula?

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 **bleep**ot 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Manager

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

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 (,).

-Jeff
2 REPLIES 2
Highlighted
Community Manager

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

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 (,).

-Jeff
Highlighted
Level V

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

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.
Article Labels

There are no labels assigned to this post.