Return the first non blank cell in column that contains a date

[vdberg20]

Hi everyone

Example:

1. I have a column E and with several blank cells, some of which contains dates. I want all the cells with dates returning the date in sequence for the entire column range E in A1, then A2, etc

2. Then next to each date in B1, B2 etc return the name (text) in column B

3. Then to return the text content in E$4$ for each of the dates it found in 1 in cell c1

sample results:

a1 b1 c1
1 January 2013 TextB Company A

 

[Robert Mika]

Hi everyone

Example:

1. I have a column E and with several blank cells, some of which contains dates. I want all the cells with dates returning the date in sequence for the entire column range E in A1, then A2, etc

2. Then next to each date in B1, B2 etc return the name (text) in column B

3. Then to return the text content in E$4$ for each of the dates it found in 1 in cell c1

sample results:

a1 b1 c1
1 January 2013 TextB Company A

Hello and welcome.
Coudl you post your data in more friendly format?
Please see my signature on how to do it.

 

[vdberg20]

-- removed inline image ---

 

[vdberg20]

[TABLE="width: 685"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client A[/TD]
[TD]Client B[/TD]
[TD]Client C[/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01 December 2013[/TD]
[TD="align: right"]01 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03 December 2013[/TD]
[TD][/TD]
[TD="align: right"]08 December 2013[/TD]
[/TR]
[TR]
[TD]Process 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08 December 2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Results Need: (in date order) formulas for column a,b,c?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD="align: right"]01 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 2[/TD]
[TD="align: right"]05 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 5[/TD]
[TD="align: right"]10 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD="align: right"]01 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD="align: right"]03 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD="align: right"]08 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 6[/TD]
[TD="align: right"]08 December 2013[/TD]
[TD]Client C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

[TABLE="width: 685"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client A[/TD]
[TD]Client B[/TD]
[TD]Client C[/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01 December 2013[/TD]
[TD="align: right"]01 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03 December 2013[/TD]
[TD][/TD]
[TD="align: right"]08 December 2013[/TD]
[/TR]
[TR]
[TD]Process 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10 November 2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08 December 2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Results Need: (in date order) formulas for column a,b,c?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD="align: right"]01 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 2[/TD]
[TD="align: right"]05 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 5[/TD]
[TD="align: right"]10 November 2013[/TD]
[TD]Client B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD="align: right"]01 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD="align: right"]03 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD="align: right"]08 December 2013[/TD]
[TD]Client A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Process 6[/TD]
[TD="align: right"]08 December 2013[/TD]
[TD]Client C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010
	A	B	C	D	E	F
1	Description			Client A	Client B	Client C
2	Process 1			1-Dec-13	1-Nov-13
3	Process 2				5-Nov-13
4	Process 3			3-Dec-13		8-Dec-13
5	Process 4
6	Process 5				10-Nov-13
7	Process 6			8-Dec-13
8	Process 7
9
10
11
12	Process 1	1-Nov-13	Client B
13	Process 2	5-Nov-13	Client B
14	Process 5	10-Nov-13	Client B
15	Process 1	1-Dec-13	Client A
16	Process 3	3-Dec-13	Client A
17	Process 3	8-Dec-13	Client C
18	Process 6	8-Dec-13	Client A
Sheet1

Cell Formulas
Range	Formula
B12	=IFERROR(SMALL($D$2:$F$8,ROW(A1)),"")
C12	=IFERROR(INDEX($D$1:$F$1,MATCH(B12,INDEX($D$2:$F$8,MATCH(A12,$A$2:$A$8,0),0),0)),"")
A12	{=IF(B12="","",IFERROR(INDEX($A$2:$A$8,SMALL(IF($D$2:$F$8=$B12,ROW($D$2:$F$8)-ROW($D$2)+1),COUNTIF($B$12:B12,B12))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[vdberg20]

Many thanks Robert, perfect solution! Thanks so much for your help and have a great weekend.

 

[Robert Mika]

Many thanks Robert, perfect solution! Thanks so much for your help and have a great weekend.

You are welcome.

 

[vdberg20]

Hi Robert, I have discovered a small problem.

If the same date is find in same data e.g. therefore as per sample data d7=e7, then the same client name is returned find in row 1?

Many thanks for your support.