Transform List to Double-entry Table

Transform List to Double-entry Table
Last Updated on 13/11/2021
Reading time: 3 minutes

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.

Distance between cities

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)

The function UNIQUE extract unique values

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)
Convert the function UNIQUE to values

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)
The cities are the headers of the rows and columns

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)

Formula to fill the distances in the cross table

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,"")

The fourth argument of XLOOKUP removes the error

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,""))

Both diagonal of the cross table are filled

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Transform List to Double-entry Table

Reading time: 3 minutes
Last Updated on 13/11/2021

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.

Distance between cities

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)

The function UNIQUE extract unique values

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)
Convert the function UNIQUE to values

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)
The cities are the headers of the rows and columns

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)

Formula to fill the distances in the cross table

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,"")

The fourth argument of XLOOKUP removes the error

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,""))

Both diagonal of the cross table are filled

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

Leave a Reply

Your email address will not be published. Required fields are marked *