Transpose Two Tables without Blank Values in Google Sheets

1
votes

I have two Tables in Google Sheet where i want to bring the values to one row with the corresponding quantity in second column from the Second Table.

First Table

+----------------------------+----------------------------+----------------------------+-----------------------------+----------------------------+---+---+----------------------------+
|             A              |             B              |             C              |              D              |             E              | F | G |             H              |
+----------------------------+----------------------------+----------------------------+-----------------------------+----------------------------+---+---+----------------------------+
| .                          | .                          | Paralite 1215 Ladies-BKB/4 |  Paralite 1215 Ladies-BKB/5 | Paralite 1215 Ladies-BKB/6 | . | . | .                          |
| Paralite 1216 Ladies-BKR/1 | Paralite 1216 Ladies-BKR/2 | .                          | .                           | .                          | . | . | .                          |
| .                          | .                          | .                          | .                           | .                          | . | . | Paralite 1217 Ladies-BKR/9 |
+----------------------------+----------------------------+----------------------------+-----------------------------+----------------------------+---+---+----------------------------+

Second Table

+---+---+---+---+---+---+---+---+
| A | B | C | D | E | F | G | H |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 8 | 6 | 8 | 0 | 0 | 0 |
| 4 | 8 | 0 | 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
+---+---+---+---+---+---+---+---+

Output I want

+----------------------------+---+
|             A              | B |
+----------------------------+---+
| Paralite 1215 Ladies-BKB/4 | 8 |
| Paralite 1215 Ladies-BKB/5 | 6 |
| Paralite 1215 Ladies-BKB/6 | 8 |
| Paralite 1216 Ladies-BKR/1 | 4 |
| Paralite 1216 Ladies-BKR/2 | 8 |
| Paralite 1217 Ladies-BKR/9 | 6 |
+----------------------------+---+

Google Sheet Link :

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

2
google-sheetsgoogle-sheets-formula
link – Ramu Angappan
can you add a desired results tab on your sample sheet? – MattKing
Welcome.Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos

2 Answers

1
votes

Try this:

=query( 
  { 
    flatten(A2:R7), 
    flatten(T2:AK7) 
  }, 
  "where Col1 is not null", 
  0 
)

If that does not cut it for you, please follow MattKing's advice and show your expected results in the sample spreadsheet. Clearly identify in the spreadsheet where the two source tables are and where your expected results are shown.

0
votes

Use the following query for 2 tables like A1:H3 and A8:H10

=QUERY({FLATTEN(A1:H3),FLATTEN(A8:H10)}, 
           " where Col1 <>'' and Col2 <>0 ",0)A1

enter image description here