CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup
paragbaxi
paragbaxi

Reputation: 4243

merge columns over multiple rows with a common column

Trying to "flatten" a Google sheet across multiple rows by using one row as the "primary key".

VBA answer in Excel: Merging Rows with common column

Tried doing Filter with Find but I am getting mismatched row errors. Not sure how to leverage VLOOKUP across multiple rows with criteria of the cell value being not blank.

Before

| animal | legs | cute |
|--------|------|------|
| dog    |      |      |
| dog    | 4    |      |
| dog    |      | yes  |
| cat    | 4    |      |

After

| animal | legs | cute |
|--------|------|------|
| dog    | 4    | yes  |
| cat    | 4    |      |

Upvotes: 1

Views: 119

Answers (1)

player0
player0

Reputation: 1

try it like this:

={A1:C1; ARRAYFORMULA({QUERY(TO_TEXT(A2:B), "where Col2 !=''", 0), 
 IFERROR(VLOOKUP(QUERY(TO_TEXT(A2:B), "select Col1 where Col2 !=''", 0), 
 SORT(A2:C, 3, 1), 3, 0))})}

0

Upvotes: 1

Related Questions