Return multi-row non-blanks with column headers from a table

[vtcc87]

Hello, experts!

I've been trying to figure out how to pull the non-blanks from a table and keep their associated column header. I've tried using INDEX, MATCH, AGGREGATE and LOOKUP in various ways and just keep getting wrapped around the axle. I attached a spreadsheet example of what I'm trying to do. The problem I run into is that I'm dealing with multiple rows. With single rows I can do it, but the LOOKUP formula only works for one entry, not two. If there's a way to use a single formula that can capture all the non-blanks, that would be great. Thanks in advance for your time!

[BMV]

Look at the attachment.

[vtcc87]

WOW!

BMV...that is a great solution. And a very quick response. I will take some time to decipher the formulas so I can understand what you did. Much thanks for your help!!

[vtcc87]

Hello, BMV. In reference to the solution you provided, it turns out that I need to add some rows above the table. How do I adjust the formula to accommodate extra rows above my table? I tried to adjust the formula but i couldn't figure it out.

For example, how do I adjust the formula if i inserted four rows above my table? Thanks in advance!


UPDATE: Disregard...I figured it out!

[BMV]

It's no problem but after insert some row above table correct part ROW(A1). it must be ROW(A1) in first row ow result table, row(a2) in second row ….
The second solution is to replace ROW(A1) to ROWS($A$1:A1) before inserting.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[vtcc87]

BMV...once again, an excellent solution! Very much appreciate you sharing your knowledge!