Tag: formulas

This Formula Challenge originally appeared as Tip #131 of my weekly Google Sheets Tips newsletter, on 21 December 2020.

Sign up here so you don’t miss out on 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:

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):

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

The formula uses the FLATTEN function to collect data from the input ranges into a single column before the UNIQUE function selects the unique ones before they are finally sorted.

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.

Split on this unique delimiter using the SPLIT function:

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

---

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!

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.

The key to it all is the CELL function, which can extract the address of a cell.

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

In this example, you’ll see how to reverse text in Google Sheets.

To start, enter some text into cell A1 and the formula in cell B1, to reverse the order of the text:

Reverse Text In Google Sheets

What’s the formula?

It’s a beast! See below for a detailed breakdown of how it works.

It might be useful if you wanted to find the last character in a string, or the last occurrence of a character using the FIND() function.

Here’s an alternative, which reverses the capitalization too (submitted by Michael D over email – thanks!):

Can I see an example worksheet?

Yes, here you go.

How does this formula work?

Basically we make an array of numbers corresponding to how many letters are in the original text string. Then we reverse that, so the array shows the largest number first. Then we extract each letter at that position (so the largest number will extract the last letter, the second largest will extract the second-to-last letter, etc., all the way to the smallest number extracting the first letter). Then we concatenate these individual letters.

Easy! Err…

The only way to really understand this formula is to break it down, starting from the inner functions and building back out.

Assuming we have the text string “Abc” in cell A1, then let’s build the formula up in cell B1, step-by-step:

Step 1:

Use the LEN function to calculate the length of the text string and turn it into a range string with “1:”&LEN(A1)

Use the INDIRECT function to turn this string range reference into a valid range reference.

Finally wrap with ROW to convert into a row number list.

which outputs a result of 1 in cell B1.

Step 2:

Turn the formula into an array formula, by hitting Ctrl + Shift + Enter, or Cmd + Shift + Enter (on a Mac), to the formula above. This will add the ArrayFormula wrapper around the formula:

This outputs 1 in cell B1, 2 in cell B2 and 3 in cell B3:

Step 3:

Reverse the output, so 3 is in cell B1, 2 in B2 and 1 in B3, by subtracting from the length of the text in A1 and adding 1 to avoid 0:

Step 4:

Use the MID formula to now extract the letters at position 3 (“c”), position 2 (“b”) and position 1 (“A”) and display in cells B1:B3:

so your output is now:

Step 5:

Concatenate so that all the individual outputs are combined into a single cell:

Step 6 (optional):

The string is essentially reversed now, so we could stop here.

However, you can use the PROPER function to capitalize the first letters of each word only, for a true reverse effect:

Step 7 (optional):

Last step is to add in an IFERROR function to avoid an unsightly error message if the input cell (A1) is blank:

Final output:

Here’s the formula in action again:

Further Reading

You might also like this article on Using Text Rotation to Create Custom Table Headers in Google Sheets.