Tag: Google Sheets ARRAYFORMULA

5 ways to create an ordered alphanumeric list in Google Sheets

For whatever reason, sometimes we just need a list of alphanumeric letters and numbers like this “0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” to run down a column or across a row, or just all packed into one string of text in a cell in our Google Sheet.

I most commonly use an alphanumeric list to index data or use an alphanumeric string in the random assignment of a value.

In this tutorial, we will cover 4 ways to achieve this. Some approaches have different benefits than others. While two of the approaches even use the new Google Sheets LAMDA function.

If you just want to dive in, copy the formula, and get back into your own project, click on the link to the main numbered headers in the table of contents below. You can copy the formula into your project using the copy symbol in the dropdown menu of the formula bar and selecting the formula you want.

Each formula type can be:

  1. Transposed to run across a row.
  2. Join into a string in a cell.
  3. Set to lowercase.
  4. Extended to include a lowercase option.

The How it Works sections are not mandatory reading but might be helpful if you plan to extend the formula or are simply curious.

Oh, and number 1 is my favorite.

Continue reading “5 ways to create an ordered alphanumeric list in Google Sheets”

Create links to the first item of each group in Google Sheets

Imagine you have a Google Sheet tab containing a course worth of hundreds of students sorted into classes or groups. After each exam, quiz or assignment, your teacher for each class needs to enter in the grades.

To make life easier for your teachers, we want to create a summary page where we can click on their class and be navigated directly to their group.

We don’t want to have to manually create a link for each location so we are going to write a formula to do the work for us.

Now, the formula is a little complex. So first off, we will generate our list of classes and add a separate section start link for each one. Then we will combine the two together to generate a single formula that gets all the unique sections and provides a link for each one.

If you are playing along, you can grab a copy of the starter sheet here:

STARTER SHEET

Continue reading “Create links to the first item of each group in Google Sheets”

Displaying Odd and Even Row Data in Google Sheets

Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?

You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.

In this tutorial, we will cover three scenarios:

  1. Separate a range of data by odd and even rows.
  2. Separate a range by odd and even where a selected column has a list of numerical ids.
  3. Separate a range by odd and even where a selected column contains a list of alphanumeric ids.

If you want to play along, you can get a copy of the starter sheet here:

Odd and Even | Starter Google Sheet*

*Contains Easter Eggs.

Continue reading “Displaying Odd and Even Row Data in Google Sheets”

Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial.

When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on IMPORTRANGE data was better than another. However, I wanted to be certain. I ran some basic tests comparing the two approaches to see how they both perform over large data sets, and you know what? I was surprised to find I couldn’t find a discernable difference in performance.

Next, I reached out to some of the other fellow Google Sheets nerds, who like to go way too far with software for all the wrong reasons, and they seemed to feel that those demi-gods of Google devs really understand the wayward predilections of their users and may have benevolently stored the IMPORTRANGE data locally in the sheet you have imported to.

Will we ever know for certain? Only ever perhaps in the lay of the tea leaves or roll of the bones. We can but only guess the ways of the Googler as they traverse the digital world in all their etheral glory.

What does this mean in short? There are two pretty solid ways to use VLOOKUP with IMPORTRANGE. I’ll cover both in this tutorial.

Continue reading “Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function”

How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

  • Automatically change your start and end times.
  • Changing your step increments from a minute to, say, every five minutes or an hour.
  • Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

  1. A whole day list of times in 1-minute intervals
  2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

Continue reading “How to create a time sequence in Google Sheets”

%d bloggers like this: