How can I extract multiple values based on one criterion?

user_9235719 0
Viewed 32

I currently have a database that looks like this:

enter image description here

But I would like for the output of the database to look like this:

enter image description here

I have tried using INDEX MATCH but I haven't been able to find a way to exclude blank values from the output.

A formula I have used but have not exactly returned the expected output is: 

=INDEX('Input sheet'!B2:I7,0,MATCH(A2,'Input sheet'!A2:A7,0))

Is there a formula that can return non-blank values in a row, as shown on the output sheet?

https://docs.google.com/spreadsheets/d/1HdkBPhzB5oZ0RyKZzAOkPIXT81upgHiGBLfgXsrOZDI/edit?usp=sharing

google-sheets-query array-formulas google-sheets-formula transpose google-sheets
1 Answers
=ARRAYFORMULA({UNIQUE(FILTER('Input sheet'!A2:A, 'Input sheet'!A2:A<>"")), TRIM(SPLIT(
 TRANSPOSE(QUERY(IF(QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE(IF(LEN('Input sheet'!B2:J), "♠"&'Input sheet'!A2:A&"♦"&'Input sheet'!B1:J1, ))
 ,,999^99))),,999^99), "♠")), "♦")),
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1",0), "offset 1",0)<>"",
 QUERY(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE(IF(LEN('Input sheet'!B2:J), "♠"&'Input sheet'!A2:A&"♦"&'Input sheet'!B1:J1, ))
 ,,999^99))),,999^99), "♠")), "♦")),
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1", 0), 
 "select Col1 offset 1", 0)&"♥", ),,999^99)), "♥"))})

0

REVERSE OF ABOVE:

=ARRAYFORMULA(TRANSPOSE(QUERY(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN('Output sheet'!B2:F), "♠"&'Output sheet'!A2:A&"♦"&'Output sheet'!B2:F, ))
 ,,999^99)),,999^99)), "♠")), "♦"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1", 0)))

0

user_56326290
Related