Find the next row containing data

[lac87]

Hi,

I have a 'draft timetable' sheet like this:

Harry	Trumpet	Wednesday	10:30
		Wednesday	11:00
Jackie	Saxophone	Wednesday	11:30
Bob	Flute	Wednesday	12noon
Jill	Trumpet	Thursday	10:30

<tbody>
</tbody>

And 'final timetable' sheet that I want to look like this (no empty cells):

Harry	Trumpet	Wednesday	10:30
Jackie	Saxophone	Wednesday	11:30
Bob	Flute	Wednesday	12noon
Jill	Trumpet	Thursday	10:30

<tbody>
</tbody>

The 'draft' sheet has about 150 rows and the empty rows are random.

Is there a formula I can use in the 'final' sheet that looks for the first row containing data in the 'draft' sheet and uses that in row 1, then looks for the next row containing data in 'draft' and uses that in row 2, etc. Not necessarily copying the entire row - I'm happy to use a separate formula in each column.

Thanks

 

[Joe4]

Welcome to the Board!

It looks as if you should be able to do this fairly easily using Advanced Filters, to Filter your data based on column A not being blank, and pasting the results to the new sheet.

 

[lac87]

Welcome to the Board!

It looks as if you should be able to do this fairly easily using Advanced Filters, to Filter your data based on column A not being blank, and pasting the results to the new sheet.

Thanks.
Yep, that's how I do it at the moment. Thought there might be a way for it to happen automatically.

 

[Aladin Akyurek]

Formula based, it can be done like this:

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Harry	Trumpet	Wednesday	10:30​		4​	1​	Harry	Trumpet	Wednesday	0.4375​
2​			Wednesday	11:00​			3​	Jackie	Saxophone	Wednesday	0.479166667​
3​	Jackie	Saxophone	Wednesday	11:30​			4​	Bob	Flute	Wednesday	12noon
4​	Bob	Flute	Wednesday	12noon			6​	Jill	Trumpet	Thursday	0.4375​
5​
6​	Jill	Trumpet	Thursday	10:30​

In F1 just enter:

=COUNTIFS(A1:A6,"?*")

In G1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$1:G1)>$F$1,"",SMALL(IF(1-($A$1:$A$6=""),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($G$1:G1)))

In H1 just enter, copy across, and down:

=IF($G1="","",IF(COLUMNS($G1:G1)>COLUMNS($A$1:$D$6),"",INDEX($A$1:$D$6,$G1,COLUMNS($G1:G1))))