5 tips and formula to blend data in Google Spreadsheet

 

You have a spreadsheet full of data. It’s messy and you don’t really know how to merge and organize all this. In this blog post, I will give you a quick overview of the best tips and formula blend data in Google Spreadsheet. If you have read my article about using Supermetrics these tips and formulas will be definitively useful. I hope it will lead my readers to the path for more efficiency. All formulas presented in this blog post are illustrated in the following spreadsheet:

https://docs.google.com/spreadsheets/d/1YIXTWTten78aqmFioeWB3E1-xR8my1o71CQQDuWv7tg/edit?usp=sharing

1— Automatically copy formulas down to fill a column using ARRAYFORMULA

\=ARRAYFORMULA(if(A2:A<>“”,“test”,“”))

ARRAYFORMULA is the bare bones of any data manipulation in Google Spreadsheet. It will save you a lot of times and force you to structure and organize efficiently your data. Thanks to this formula you can automatically copy formulas and value down to fill a column. It is a must when working with data dynamically imported in a Google Spreadsheet. It means that formulas will be applied dynamically to any numbers of rows of data imported. ARRAYFORMULA is particularly useful when associated with other formulas as illustrated in the following paragraph.

2— Combine data from different columns with VLOOKUP and ARRAYFORMULA

\=ARRAYFORMULA(if(A2:A<>“”,VLOOKUP(A2:A,‘raw data’!A:C,2,false),“”))

VLOOKUP allows you to associate values from different columns in different or similar sheets based on a common value from another column. In other words, it allows you to merge data around unique identifiers. I’m typically using it when I want to associate campaign conversion from various sources. In that case, I’m usually using the date as the unique identifiers. In the next point, I will share a tip on how to create a unique ID in every Google Spreadsheet.

3— Create a unique ID to easily lookup values

\=ARRAYFORMULA(if(A2:A<>“”,A2:A&B2:B,“”))

When using VLOOKUP formula you always need to have a common value to lookup for the values you want to associate. This common value must be unique and you sometimes need to create it. You can create it by mixing whatever value you want using the ARRAYFORMULA. You can mix date and campaign name, value, and date, two specifics dimensions, etc…. you just need to make sure that whatever mixing values (metrics/dimensions) give you a unique output.

4— Return set of data filtered with multiple conditions using the query function

\=query(‘raw data’!A:D,“select B,sum(D) where C = ‘foo’ group by C,B label sum(D) “, B “”)

The query function might be scary for beginners but it can really make your life much easier. As it as some kind of similarity with SQL, it is also a great introduction to SQL. With the query function, you select a range of data on which you can perform calculation and apply filters. You can filter a column, some specifics rows, calculate an average, rename column headers, reorder rows, etc… The function works with various clauses: select, where, group, pivot, order, limit, etc… If you have never used it definitively take a look at the query function in action in the attached Google spreadsheet and read the following articles:

5 - Merge multiple ranges of data from multiple sheets with QUERY and ARRAYFORMULA

\=query(arrayformula({iferror(Sheet1!A1:A/0, “dataset1”), Sheet1!A1:A; iferror(Sheet2!A1:A/0, “dataset2”), Sheet2!A1:A}), “select * where Col2 <> “”)

This formula looks scary but It’s not that complicated. It allows you to merge multiple datasets and by doing so adding a new column with the name of each data set. For the formula to work correctly you will need to make sure to have the exact same number and order of columns. Let’s say you have data set A and data set B in two differents sheets with metrics 1,2,3 for each data data set. By applying this formula in a third sheet you will have a consolidated data set with 3 columns for metrics 1,2,3 and one another columns with two values data set A and data set B.

Resources

In order to conclude this article, I’d like to share some resources. I’ve already shared these links but I’d like to make sure you don’t miss them: