Creating a list of only values from column with blank cells

[cannelen]

I'm looking to dynamically create a consolidated list from a column of data that has blank rows.

ex.
[TABLE="width: 200"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD]date1[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD]date2[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD]date3[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Without copying and sorting or removing blank rows I would like to return a list of only the dates one after another like this:
date1
date2
date3

The rows that have dates will be changing and the dates themselves will change, there is no formula to how they are appear in the list and all the values in column A are all the same.


I tried to use some sort of index/small/countblank combination but I'm not really familiar with those so I couldn't get it figured out.
Thanks!

 

[JoeMo]

Welcome to the Board.

Is this what you want (adjust ranges to suit)?

Excel Workbook
	A	B	C	D	E	F
1	blah					List Dates
2	blah	1/5/2014			1	1/5/2014
3	blah				2	2/12/2014
4	blah				3	3/6/2014
5	blah				4	4/1/2014
6	blah
7	blah	2/12/2014
8	blah
9	blah
10	blah
11	blah
12	blah	3/6/2014
13	blah
14	blah
15	blah
16	blah
17	blah
18	blah	4/1/2014
19	blah
20	blah
Sheet1

 

[Aladin Akyurek]

In case the dates are not in ascending order and you want to keep the occurrence sequence...

F2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$20,SMALL(IF(ISNUMBER($B$1:$B$20),Ivec),ROWS($F$2:F2))),"")

where Ivec is defined using Formulas | Name Manager as referring to:

=ROW(Sheet1!$B$1:$B$20)-ROW(Sheet1!$B$1)+1

Adjust for the sheet name and range to suit.

 

[cannelen]

Thanks! They both worked perfectly!