This article will explain to you how to convert a list of values to a double-entry table.

Distance between cities

The following file gives you a list of the distance (in km) between the 15 biggest cities in the USA. You can easily calculate the distance between cities with an API Google Maps as is explained in this article.

Step 1: Keep cities without duplicates

First, we must keep only unique cities and with the new UNIQUE function of Microsoft 365, it's very easy to do that.

=UNIQUE(A2:A106)

Step 2: Convert the formula to values

Convert this formula to values with the option Copy-Paste Special Values.

1. Select all the values returned by the function UNIQUE
2. Copy the values (Ctrl + C)
3. Paste Special Value (Ctrl + Alt + V and V again)

Step 3: Copy and Transpose the cities

And now, we also need to copy the cities in columns

1. Select all the cities
2. Copy the values (Ctrl + C)
3. Paste Special Transpose (Ctrl + Alt + V and E for transpose)

Step 4: Formula to copy the distance

Now, we have finished creating the table. So now, it's time to fill the cells in the function of the couple (city1 - city2). To do this, we will use the function XLOOKUP (with Microsoft 365 or Excel Online only)

The function XLOOKUP is impressive because we can build research based on the content of 2 columns

1. The value to search is the combination of the row 1 & the column E
2. The reference table is the columns A and B
3. The result is the column C (the distance)

So the formula to populate the double-entry table is

=XLOOKUP(F$1&"-"&$E2,$A$2:$A$106&"-"&$B$2:$B$106,$C$2:$C$106)

If you are not familiar with the $, have a look at this article to understand why I have put a dollar before the 1 and a dollar before the E.

Also, the sign "-" isn't necessary but just better to slipt the contain of the 2 columns (first and second argument)

Step 5: Remove the #N/A

The error #N/A means Not Applicable (or I haven't found it). So, to avoid returning an error when the formula doesn't have found the combination between the cities, we can fill the 4th argument of the XLOOKUP function with "" (2 double-quotes)

=XLOOKUP(F$1&"-"&$E2,$A$2:$A$106&"-"&$B$2:$B$106,$C$2:$C$106,"")

Step 6: Fill both diagonals

Now if you want to fill both sides of the diagonals, you have created research for city1-city2 and also city2-city1. In this case, the formula is this one.

=IF(
ISNA(XLOOKUP(F$1&"-"&$E2,$A$2:$A$106&"-"&$B$2:$B$106,$C$2:$C$106)),
XLOOKUP($E2&"-"&F$1,$A$2:$A$106&"-"&$B$2:$B$106,$C$2:$C$106,""),
XLOOKUP(F$1&"-"&$E2,$A$2:$A$106&"-"&$B$2:$B$106,$C$2:$C$106,""))

And just like that, with a single formula, you transform your list of data in a table with a double-entry 😎👍