Listing Noncontiguous Data Without Blanks

Hello everybody,

I have a list with As, Bs and "blanks".

"Blank" means that the cell is not empty, but contains a formula leading to a "" and looks empty.

I'd like to obtain a list without blanks using a (matrix?) formula.

I attached a sample file.

Can somebody help me?

Florian

It is usually referred to as an "array" formula, entered using the key combination of Ctrl-Shift-Enter, but here's an approach which just uses two normally-entered formulae. Put this formula in B4:

=IF(OR(C4="A",C4="B"),MAX(B$3:B3)+1,"")

then copy down to the bottom of your list. It will result in a unique sequential number being allocated to those records which match the criteria. Then you can use this formula in C22:

=IFERROR(INDEX($C$4:$C$12,MATCH(ROWS($1:1),$B$4:$B$12,0)),"")

and copy this down as far as you need to. It just returns the records in order of those sequential numbers, and omits any blank records (or, indeed, any records which do not contain A or B).

Hope this helps.

Pete

Dear Tsjallie, dear Pete,

thanks a lot for your formulas / solutions.

Every of these works fine; I am glad to have two solutions now.

Regards

Florian

It's good that you have two solutions, and I see that you have marked the thread as Solved.

You might like to know, though, that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete