I have one large spreadsheet with names, addresses, phone numbers, emails, Etc. Some records have a second address for which I have a column named "Address 2" I was hopping to write a query that would give me an output with duplicate rows of which the only difference was the "Address 2" column would be in the main address Column.
Data:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Status | Name | Address | Phone | Address2 | Hire Date | |
| 2 | Joe Smith | 123 Smith St | 201 555 3099 | Joe@stackoverflow.com | 7th Avenue Sq | ||
| 4 | Q | Jane Smith | 321 Not Smith St | 12/15/1980 | |||
| 5 | Robert Smith | 818 555 4321 | Robert@googlesheets.com | 12/13/1981 |
Looking for an Query output to look like:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Status | Name | Address | Phone | Hire Date | |
| 2 | Joe Smith | 123 Smith St | 201 555 3099 | Joe@stackoverflow.com | ||
| 3 | Joe Smith | 7th Avenue Sq | 201 555 3099 | Joe@stackoverflow.com | ||
| 4 | Q | Jane Smith | 321 Not Smith St | 12/15/1980 | ||
| 5 | Robert Smith | 818 555 4321 | Robert@googlesheets.com | 12/13/1981 |
I was trying something like:
=QUERY({Sheet1!$A2:$G,Sheet1!$B2:$B,Sheet1!$F2:$J },"SELECT Col1, Col2, Col3, Col4, Col5, Col7 JOIN Col6 ON Col2 = Col2")
Which I think is more or less how it would be in SQL, but Google sheets doesn't have a join function.
Is there any way to get this done?
most simple you can do is:
=QUERY({A1:E, G1:G; A2:B, F2:F, D2:E, G2:G}, "where Col3 is not null", )