Formula Challenge #5: Merge Columns in Google Sheets With Single Range Reference

This Formula Challenge originally appeared as Tip #131 of my weekly Google Sheets Tips newsletter, on 21 December 2020. Sign up so you don’t miss future Formula Challenges!

Find all the Formula Challenges archived here.

The Challenge: Merge Columns With Single Range Reference

Question: How can you merge n number of columns and get the unique values, without typing each one out?

In other words, can you create a single formula that gives the same output as this one:

=SORT( UNIQUE( {A:A;B:B;C:C;...;XX:XX} ))

but without having to write out A:A, B:B, C:C, D:D etc. and instead just write A:XX as in the input?

Use this simple dataset example, where your formula will be in cell E1 (in green):

Formula challenge 5 Data

Your answer should:

  • be a formula in a single cell
  • work with the input range in the form A:XX (e.g. A:C in this example)
  • work with numbers and/or text values
  • return only the unique values, in an ascending order in a SINGLE COLUMN.

Solutions To Sort A Column By Last Name

I received 67 replies to this formula challenge with two different methods for solving it. Congratulations to everyone who took part!

I learnt so much from the different replies, many of which proffered a shorter and more elegant second solution than my own original formula.

Here I present the two solutions.

There’s a lot to learn by looking through them.

1. FLATTEN method

=SORT(UNIQUE(FLATTEN(A:C)))

Flatten collects all the data from the ranges into a single column, before the unique ones are selected and then sorted.

The FLATTEN function is a new function that popped up in early 2020, but was only recently documented by Google.

It’s a powerful formula, and one that can be used to unpivot data.

Note 1: you can have multiple inputs (arguments) to the FLATTEN function. Data is ordered by the order of the inputs, then row and then column.

Note 2: at the moment the FLATTEN function doesn’t show up in the auto-complete when you start typing it out. You can still use it, but you’ll have to type it out fully yourself.

Thanks to the handful of you that shared this neat solution with me. Great work!

2. TEXTJOIN method

Join all the values in A:C with TEXTJOIN, using a unique character as the delimiter (in this case, the King and Queen chess pieces!)

You want to use an identifier that is not in columns A to C.

=TEXTJOIN("♔♕",TRUE,A:C)

Split on this unique delimiter:

=SPLIT(TEXTJOIN("♔♕",TRUE,A:C),"♔♕")

Transpose to a column, select the unique values only and finally wrap with a sort function to get the result:

=SORT(UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN("♔♕",TRUE,A:C),"♔♕"))))

There we go!

Two brilliant solutions to an interesting formula challenge.

Please leave comments if you have anything you wish to add.

And don’t forget to sign up to my Google Sheets Tips newsletter so you don’t miss future formula challenges!

Google Sheets Sort By Color And Google Sheets Filter By Color

Google Sheets sort by color and filter by color are useful techniques to organize your data based on the color of text or cells within the data.

For example, you might highlight rows of data relating to an important customer. Google Sheets sort by color and filter by color let you bring those highlighted rows to the top of your dataset, or even only show those rows.

They’re really helpful for removing duplicates in Google Sheets too.

As a bonus, they’re really easy to use. Let’s see how:

Google Sheets Sort By Color

Suppose you have a dataset with highlighted rows, for example all the apartments in this dataset:

Google Sheets dataset

Add a filter (the funnel icon in the toolbar, shown in red in the above image).

On any of the columns, click the filter and choose the “Sort by color” option.

You can filter by the background color of the cell (like the yellow in this example) or by the color of the text.

Google Sheets Sort By Color

The result of applying this sort is all the colored rows will be brought to the top of your dataset.

Data sorted by color in Google Sheets

This is super helpful if you want to review all items at the same time. Another reason might be if they’re duplicate rows you’ve highlighted which you can now delete.

Google Sheets Filter By Color

The Google Sheets filter by color method is very similar to the sort by color method.

With the filters added to your dataset, click one to bring up the menu. Select “Filter by color” and then select to filter on the background cell color or the text color.

Google Sheets filter by color

In this example, I’ve used the Google Sheets filter by color to only display the yellow highlighted rows, which makes it really easy to review them.

Data filtered by color in Google Sheets

There’s an option to remove the filter by color by setting it to none, found under the filter by color menu. This option is not found for the sort by color method.

Apps Script Solution

📍 When I originally published this article, sort by color and filter by color were not available natively in Google Sheets, so I created a small script to add this functionality to a Sheet.

They were added on 11th March 2020. Read more here in the Google Workspace update blog.

Here is my original Apps Script solution, left here for general interest.

With a few simple lines of Apps Script, we can implement our own version.

Filter By Color in Google Sheets

This article will show you how to implement that same feature in Google Sheets.

It’s a pretty basic idea.

We need to know the background color of the cell we want to sort or filter with (user input 1). Then we need to know which column to use to do the sorting or filtering (user input 2). Finally we need to do the sort or filter.

So step one is to to prompt the user to input the cell and columns.

I’ve implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, we store this using the Properties Service for retrieval when we come to sort or filter the data.

Apps Script Sort By Color

At a high level, our program has the following components:

  1. Custom menu to run the Google Sheets sort by color program
  2. Prompt to ask user for the color cell
  3. Save the color cell using the Properties Service
  4. Second prompt to ask the user for the sort/filter column
  5. Save the sort/filter column using the Properties Service
  6. Show the color and column choices and confirm
  7. Retrieve the background colors of the sort/filter column
  8. Add helper column to data in Sheet with these background colors
  9. Sort/Filter this helper column, based on the color cell
  10. Clear out the values in the document Properties store

Let’s look at each of these sections in turn.

Add A Custom Menu (1)

This is simply boilerplate Apps Script code to add a custom menu to your Google Sheet:

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}

Prompt The User For Cell And Column Choices (2, 4 and 6 above)

I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they want to select.

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
  
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title='No Title';
  var msg = 'No Text';
  
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }
  
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
      
      title = 'Select Sort Column';
      msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
      msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
      dispStatus(title, msg);
  }
  
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
    
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '<p>Confirm ranges before sorting:</p>';
    msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title,msg);
    
  }
}

/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
  
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
 
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);

}

/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
  
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}

The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.

Following this, the google.script.host.close() is also a client-side JavaScript API that closes the current dialog box.

Save The Cell And Column Choices In The Property Store (3 and 5)

These two functions save the cell and column ranges that the user highlights into the Sheet’s property store:

/** 
 * saves the color cell range to properties
 */
function setColorCell() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);

}

/**
 * saves the sort column range in properties
 */
function setSortColumn() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
  
}

As a result of running these functions, we have the color cell address (in A1 notation) and the sort/filter column letter saved in the Property store for future access.

Sorting The Data (7, 8 and 9 above)

Once we’ve selected both the color cell and sort column, the program flow directs us to actually go ahead and sort the data. This is the button in the third dialog box, which, when clicked, runs this call google.script.run.sortData();.

The sortData function is defined as follows:

/** 
 * sort the data based on color cell and chosen column
 */
function sortData() {
  
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');

  // extracts column letter from whatever range has been highlighted for the sort column
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
  
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
  
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
  
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
  
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
  
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);  
  dataRange.sort(lastCol+1);
  
  // add new filter across whole data table
  sheet.getDataRange().createFilter();

  // clear out the properties so it's ready to run again
  clearProperties();
}

And finally, we want a way to clear the properties store so we can start over.

Clear The Property Store (10 above)

This simple function will delete all the key/value pairs stored in the Sheet’s property store:

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

Run The Google Sheets Sort By Color Script

If you put all these code snippets together in your Code.gs file, you should be able to run onOpen, authorize your script and then run the sort by color tool from the new custom menu.

Here’s the sort by color tool in action in Google Sheets:

Google Sheets sort by color

You can see how all of the green shaded rows are sorted to the top of my dataset.

Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).

Some improvements would be to make it more generalized (or prompt the user to highlight the dataset initially). I also have not included any error handling, intentionally to keep the script as simple as possible to aid understanding. However, this is something you’d want to consider if you want to make this solution more robust.

Apps Script Sort By Color Template

Here’s the Google Sheet template for you to copy.

(If you’re prompted for permission to open this, it’s because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You can talk to your Google Workspace administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

If GitHub is your thing, here’s the sort by color code in my Apps Script repo on GitHub.

Apps Script Filter By Color

The program flow is virtually identical, except that we filter the data rather than sort it. The code is almost exactly the same too, other than variable names being different and implementing a filter instead of a sort.

Rather than sorting the data, we create and add a filter to the dataset to show only the rows shaded with the matching colors:

Filter By Color in Google Sheets

The filter portion of the code looks like this:

// remove existing filter to the data range
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();

// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);

// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

If you want a challenge, see if you can modify the sort code to work with the filter example.

Apps Script Filter By Color Template

Feel free to copy the Google Sheets filter by color template here.

(If you’re prompted for permission to open this, it’s because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You can talk to your Google Workspace administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

Or pull the code directly from the GitHub repo here.

Experiments With Cell Function: Create A Dynamic Table Of Contents In Google Sheets With Formulas

This post is an exploration of what’s possible with formulas alone.

I’m going to use formulas to create a dynamic table of contents that updates automatically when you change the sheet names. Continue reading Experiments With Cell Function: Create A Dynamic Table Of Contents In Google Sheets With Formulas

How To Add Subscript and Superscript In Google Sheets

In this tutorial, you’ll learn how to add a subscript or superscript in Google Sheets.

Superscript In Google Sheets

Superscript And Subscript Google Sheets Template

All of the superscript and subscript characters and CHAR function equivalents are available in this free template:

Superscript Google Sheets Template

Superscript Template

Open it and make your own copy from the menu: File > Make a copy

Superscript In Google Sheets

At the moment there is no built-in option to add a superscript in Google Sheets (or subscript), so we have to be creative to achieve the effect.

Method 1: Copy Paste Unicode Characters

  1. Go to the website Graphemica, which is a great site to search for unicode characters
  2. Type in the symbol you want, using words instead of numbers e.g. “superscript two”
  3. Copy the result (e.g. for superscript two)
  4. Paste into your Google Sheet in the formula bar
  5. To get multiple superscripts next to each other, just copy them multiple times e.g. ¹ then ² to get ¹²

Here’s a list of superscripts, which you can copy:

Character Superscript
0
1 ¹
2 ²
3 ³
4
5
6
7
8
9
a
b
c
d ͩ
e ͤ
f
g
h ͪ
i ͥ
j ʲ
k
l ˡ
m ͫ
n
o ͦ
p
q Not available (why?)
r ͬ
s ˢ
t ͭ
u ͧ
v ͮ
w ʷ
x ˣ
y ʸ
z
+
=
(
)

Method 2: Create Superscripts in Google Sheets with the CHAR function

The CHAR function converts a number into a character according to the current Unicode table.

It’s a fun and amazing function.

It lets you add weird and wonderful pictures to your Google Sheets using formulas, such as this Christmas tree (from my newsletter) or this turkey:

Turkey in Google Sheets

Anyway, I’m getting carried away. You came here to learn how to add a superscript not a turkey.

Back on the superscript two Graphemica page (see Method 1 above), copy the number part of the HTML Entity (Decimal), shown in red:

graphemica superscript two

Enter this formula in your Google Sheet:

=CHAR(178)

char function Google Sheets

With this in mind, here’s how you can create Einstein’s famous mass-energy formula:

="E = mc"&CHAR(178)

Mass Energy Equation in Google Sheets

(Quick aside: You can use the CHAR function embedded in other formulas, like any other function.

For instance, here’s how to use the CHAR function to Create Custom Table Headers in Google Sheets Using Text Rotation.)

Subscript In Google Sheets

Add subscripts in the same way you added superscripts to your Google Sheets.

Method 1: Copy Paste Unicode Characters

Search Graphemica and copy-paste the subscript into your Google Sheet, for example subscript two.

Here are the subscript characters:

Character Superscript
0
1
2
3
4
5
6
7
8
9
a
e
i
o
r
u
v
x
y
+
=
(
)

Method 2: Subscript in Google Sheets Using the CHAR function

Consider the chemical makeup of water: two hydrogen atoms and one oxygen atom, written H₂O.

In the same vein as the superscript example, you can use the CHAR function to create the subscript.

Using Graphemica, search for the subscript you want and take the number from the HTML Entity (Decimal):

graphemica

Insert this into your CHAR function to create the subscript in Google Sheets:

=CHAR(8322)

Building on this, the full formula for water is:

="H"&CHAR(8322)&"O"

Subscript In Google Sheets

Sugar Example

Here’s a more complex chemical compound — sugar — which has the molecular formula C₁₂H₂₂O₁₁

="C"&CHAR(8321)&CHAR(8322)&"H"&CHAR(8322)&CHAR(8322)&"O"&CHAR(8321)&CHAR(8321)

Subscript In Google Sheets

Last Resort: Font Size

You can also achieve a subscript effect by changing the font size of the subscript digits in your text.

For example, setting the main text to font size 10 and the subscript to font size 6 will create a subscript effect.

However, I wouldn’t advocate this method because it’s tedious and not robust. But if you’re in a pinch and need to a unique subscript that you can’t find elsewhere, maybe it’ll help you out.