I have three sheets in a workbook where people enter data (text values) in different columns, with different row lengths.
For example:
Sheet 1
| Group 1 | Group 2 | Group 3 |
|---|---|---|
| Apple | Apple | Apple |
| Orange | Orange | Banana |
| Banana | Peach | |
| Pear |
Sheet 2
| Group 1 | Group 2 | Group 3 |
|---|---|---|
| Onion | Onion | Onion |
| Tomato | Tomato | Leek |
| Leek | Garlic | |
| Potato |
I'm looking to combine this data into a single sheet, displayed as such:
| Group 1 | Group 2 | Group 3 |
|---|---|---|
| Apple | Apple | Apple |
| Orange | Orange | Banana |
| Onion | Banana | Peach |
| Tomato | Pear | Onion |
| Leek | Onion | Leek |
| Tomato | Garlic | |
| Potato |
I've tried this formula:
=QUERY({Sheet1!A3:G;Sheet2!A3:G;Sheet3!A3:G},"select * where Col1<>'' or Col2<>'' or Col3<>''",0)
But it adds in blanks for as many as the longest column is on each sheet, like so:
| Group 1 |
|---|
| Apple |
| Orange |
| Onion |
| Tomato |
| Leek |
Is there anything I can change to have it just list the items per column in the order queried, skipping blank cells as opposed to rows? I found lots of guidance in other questions about consolidating into a single column, but I want to keep the columns separated and consolidate rows instead.
CodePudding user response：
You have to use multiple queries, one for each column. Even after that, we can't stack the arrays horizontally using {,} because arrays are jagged(