Combining non-contiguous ranges

[twintrbl23]

Apologies in advance if my description is vague. I'm not even sure how to describe what I'm trying to do. I have a range on a worksheet that will be used for data entry. There are a potential of 200 rows in this range. I need to repeat what was entered in this range on another tab, but without including any potential blank rows. In a super simplified scenario:

Excel 2010
	A	B	C	D	E
2	SOURCE		RESULT	OR	ALTERNATE RESULT
3	Apples		Apples		A5
4	Carrots		Carrots		A6
5	Bananas		Bananas		A7
6			Pineapple		A9
7	Pineapple		Grapes		A10
8	Grapes		Cherries		A13
9			Oranges		A14
10			Pears		A15
11	Cherries		Lemons		A16
12	Oranges
13	Pears
14	Lemons
Sheet2

As you can see, the Source column has some blank rows, and in the RESULT range, I need to remove the blanks. If it were really this small a range, I might put in Column C a formula that says "=IF(A1<>"",A1,IF(A2<>"",A2,IF(A3<>""......." and so on. But with 200 rows, that's not viable.

I tried using MATCH to find the next non-blank row and using that as an offset to the formula, but when you have two blank rows, it doesn't pick up on the fact that I'm not looking for the first non-blank anymore (since I caught that in the row above), and trying to find the second non-blank after that... well, I get into another endless nested formula again.

I tried playing with some array formulas, but they just copied the blanks too. The third column for an alternate result would work too, as I'd use that as the source for an INDIRECT formula.

Unfortunately, I need to avoid VBA code, if possible. The end users won't be manually copy and pasting, nor will they want to run macros. So I'm hunting for a super formula (or set of nested formulas) that can duplicate that huge data entry range without the blanks. I know in a pinch, I can set up the RESULT area as a filtered table, then use an auto filter to hide the non-blank rows, but that would also require a macro to run.

Any suggestions?

 

[Marcelo Branco]

Try this array formula in C3 copied down (Excel 2007 or higher)

=IFERROR(INDEX($A:$A,SMALL(IF($A$3:$A$300<>"",ROW($A$3:$A$300)),ROWS(C$3:C3))),"")

Hope this helps

M.

 

[twintrbl23]

It works!!! Thank you!!! Now I'm going to have to go hunting to figure out why that works (just because I'm a nerd and want to know everything ), but YAY!!! Thank you!

 

[Marcelo Branco]

It works!!! Thank you!!! Now I'm going to have to go hunting to figure out why that works (just because I'm a nerd and want to know everything ), but YAY!!! Thank you!

You are welcome and thanks for the feedback.

M.