Google Sheets Formulas Like a Relational Database

 

How to Create and Use Google Sheets Formulas Like a Relational Database

Google Sheets is a powerful tool that can be used to manage and analyze data. By using formulas, you can create relationships between different sheets, similar to how relational databases work. Here’s a step-by-step guide on how to set this up:

1. Setting Up Your Sheets

First, organize your data into separate sheets. For example, let’s say you have three sheets: Staff, Activity, and Implementation.

• Staff Sheet: Contains StaffId and StaffName.

• Activity Sheet: Contains ActivityId and ActivityName.

• Implementation Sheet: Contains Id, StaffId, and ActivityId.

2. Using the QUERY Function

In a new sheet (let’s call it QueryVlookUp), you can use the QUERY function to pull data from the Implementation sheet.

Cell A1 in QueryVlookUp Sheet: =QUERY(Implementation!A1:C)

This formula retrieves all data from columns A to C in the Implementation sheet.

Cell D2 in QueryVlookUp Sheet: =IFERROR(ARRAYFORMULA(VLOOKUP(B2:B, Staff!A2:B, 2)), "")

This formula performs a VLOOKUP using an array. It looks for values in column B2:B and searches for matches in the "Staff" sheet (range A2:B), returning corresponding values from the second column. IFERROR ensures empty cells when no match is found.

Cell E2 in QueryVlookUp Sheet: =IFERROR(ARRAYFORMULA(VLOOKUP(C2:C, Activity!A2:B, 2)), "")

Similarly, this formula looks up the ActivityId from column C in the QueryVlookUp sheet, finds the corresponding ActivityName from the Activity sheet, and displays it in column E. It also handles errors by returning an empty string if no match is found.

.......

Let’s break down the formula we have in cell C1 of the Staff sheet:

=ArrayFormula(trim(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QueryVlookUp!E1:E200&","),(A1:A200=TRANSPOSE(QueryVlookUp!B1:B200))*(LEN(A1:A200)>0))&REPT(CHAR(9)&" "&CHAR(9),TRANSPOSE(ROW(QueryVlookUp!B1:B200))=ROWS(QueryVlookUp!B1:B200))),","&CHAR(9),0))))

This formula is quite complex, so let’s break it down step by step:

1. QueryVlookUp!E1:E200: This range refers to the ActivityName column in the QueryVlookUp sheet.
2. A1:A200=TRANSPOSE(QueryVlookUp!B1:B200): This part checks if the StaffId in the Staff sheet matches the StaffId in the QueryVlookUp sheet.
3. LEN(A1:A200)>0: This ensures that only non-empty StaffId values are considered.
4. REPT(TRANSPOSE(QueryVlookUp!E1:E200&","), (A1:A200=TRANSPOSE(QueryVlookUp!B1:B200))*(LEN(A1:A200)>0)): This part repeats the ActivityName for each matching StaffId.
5. REPT(CHAR(9)&" "&CHAR(9), TRANSPOSE(ROW(QueryVlookUp!B1:B200))=ROWS(QueryVlookUp!B1:B200)): This adds a tab character and a space for formatting purposes.
6. CONCATENATE(...): This concatenates all the repeated ActivityName values into a single string.
7. SPLIT(..., ","&CHAR(9), 0): This splits the concatenated string by the comma and tab characters.
8. TRANSPOSE(...): This transposes the split values from rows to columns.
9. TRIM(...): This removes any leading or trailing spaces from the transposed values.
10. ARRAYFORMULA(...): This allows the formula to process an array of values instead of a single value.

In summary, this formula is designed to create a list of ActivityName values for each StaffId in the Staff sheet, based on the data in the QueryVlookUp sheet. It concatenates the ActivityName values, splits them by a delimiter, transposes the result, and trims any extra spaces.

let’s break down the formula you have in cell C1 of the Activity sheet:

=ArrayFormula(trim(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QueryVlookUp!D1:D100&","),(A1:A100=TRANSPOSE(QueryVlookUp!C1:C100))*(LEN(A1:A100)>0))&REPT(CHAR(9)&" "&CHAR(9),TRANSPOSE(ROW(QueryVlookUp!C1:C100))=ROWS(QueryVlookUp!C1:C100))),","&CHAR(9),0))))

This formula is similar to the one in the Staff sheet but adapted for the Activity sheet. Here’s a step-by-step explanation:

1. QueryVlookUp!D1:D100: This range refers to the StaffName column in the QueryVlookUp sheet.
2. A1:A100=TRANSPOSE(QueryVlookUp!C1:C100): This part checks if the ActivityId in the Activity sheet matches the ActivityId in the QueryVlookUp sheet.
3. LEN(A1:A100)>0: This ensures that only non-empty ActivityId values are considered.
4. REPT(TRANSPOSE(QueryVlookUp!D1:D100&","), (A1:A100=TRANSPOSE(QueryVlookUp!C1:C100))*(LEN(A1:A100)>0)): This part repeats the StaffName for each matching ActivityId.
5. REPT(CHAR(9)&" "&CHAR(9), TRANSPOSE(ROW(QueryVlookUp!C1:C100))=ROWS(QueryVlookUp!C1:C100)): This adds a tab character and a space for formatting purposes.
6. CONCATENATE(...): This concatenates all the repeated StaffName values into a single string.
7. SPLIT(..., ","&CHAR(9), 0): This splits the concatenated string by the comma and tab characters.
8. TRANSPOSE(...): This transposes the split values from rows to columns.
9. TRIM(...): This removes any leading or trailing spaces from the transposed values.
10. ARRAYFORMULA(...): This allows the formula to process an array of values instead of a single value.

In summary, this formula is designed to create a list of StaffName values for each ActivityId in the Activity sheet, based on the data in the QueryVlookUp sheet. It concatenates the StaffName values, splits them by a delimiter, transposes the result, and trims any extra spaces.

Make copy>>PSS Example - Query VLookUp Array - Google Sheets

Share This

Facebook

Twitter

Google+

LinkedIn

Related Posts