Create a column list out of table (rows and columns) while removing blanks

Hello all,

I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple. Thanks!

Formula:

Please Login or Register  to view this content.

Try this...

Data Range

	A	B	C	D
1	List1	List2		Combined
2	1	9		1
3	2			2
4				3
5	3	10		4
6				5
7	4			6
8	5	11		7
9	6			8
10	7			9
11		12		10
12	8			11
13				12
14	------	------	------	------

This array formula** entered in D2:

=IFERROR(IFERROR(INDEX(A:A,SMALL(IF(A$2:A$12<>"",ROW(A$2:A$12)),ROWS(D$2:D2))),INDEX(B:B,SMALL(IF(B$2:B$12<>"",ROW(B$2:B$12)),ROWS(D$2:D2)-COUNTIF(A$2:A$12,"<>")))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Change your formulas in columns A, B and C to remove the quotes " " from around the 0.

Change "0" to 0.

When you quote numbers they become TEXT strings. 0 and "0" are not the same!

Then change the array formula** in E38 to:

=IFERROR(IFERROR(INDEX(A:A,SMALL(IF($A$38:$A$49<>0,ROW($A$38:$A$49)),ROWS(E$38:E38))),INDEX(B:B,SMALL(IF($B$38:$B$49<>0,ROW($B$38:$B$49)),ROWS(E$38:E38)-COUNTIF($A$38:$A$49,"<>0")))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Good deal. Thanks for the feedback!

If I may ask one more thing, I am obviously quite the noob. I tried to add the third column C to your formula, and it wont accept it no matter what I try! Would this require a nested IFERROR function or a nested IF function? I can't get it to work. It keeps saying I have too few arguments, even though all my parenthesis are in order. BTW, I swear this is the last question about this. Thanks.

Like this...

=IFERROR(IFERROR(IFERROR(INDEX(A:A,SMALL(IF($A$38:$A$49<>0,ROW($A$38:$A$49)),ROWS(E$38:E38))),INDEX(B:B,SMALL(IF($B$38:$B$49<>0,ROW($B$38:$B$49)),ROWS(E$38:E38)-COUNTIF($A$38:$A$49,"<>0")))),INDEX(C:C,SMALL(IF($C$38:$C$49<>0,ROW($C$38:$C$49)),ROWS(E$38:E38)-COUNTIF($A$38:$B$49,"<>0")))),"")

Still array entered!

As you can see, it's getting to be quite long. Hopefully that's all the columns that need to be consolidated.

Originally Posted by jackabs333

How do I even begin to learn things like that!?

Well, you just did!

Now you just need to remember it which is the hard part!

What I do is I save everything I learn in small sample files. Then, when I need to do something I just look for the sample instead of starting from scratch.