Extracting cell values but keeping out the blanks in original order.

Hi,

I've got a challange related to extracting numbers in the original order from the below table (top image - don't know how to remove the second one).

I want to extract the Y values from the table in seperate rows, as well as getting the associated X values in another row.

I need to use a formula, as the original dataset is a dynamic table.

Let me know if any additional information is needed.

/Thanx


Excel.PNG

if we assume your source values (incl. headers) is A1:H3 then

Y - where first "A" result is to appear in B6

=IFERROR(AGGREGATE(15;6;MOD((COLUMN($B2:$H2)*1000+($B2:$H2))/ISNUMBER($B2:$H2);1000);COLUMNS($B6:B6));"")
applied to matrix B6:H7 (where row 7 is "B")

X - where first "A" result is to appear in B10

=IFERROR(AGGREGATE(15;6;MOD((COLUMN($B$1:$H$1)*1000+($B$1:$H$1))/ISNUMBER($B2:$H2);1000);COLUMNS($B10:B10));"")
applied to matrix B10:H11 (where row 11 is "B")

the only real difference for the "X" is that you reference row 1 given this should determine result -- highlighted change (from Y) in red

note: ; delimiter assumed given locale - modify as required