Find the next row containing data

[lac87]

Hi,

I have a 'draft timetable' sheet like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Harry[/TD]
[TD]Trumpet[/TD]
[TD]Wednesday[/TD]
[TD]10:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Wednesday[/TD]
[TD]11:00[/TD]
[/TR]
[TR]
[TD]Jackie[/TD]
[TD]Saxophone[/TD]
[TD]Wednesday[/TD]
[TD]11:30[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Flute[/TD]
[TD]Wednesday[/TD]
[TD]12noon[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]Trumpet[/TD]
[TD]Thursday[/TD]
[TD]10:30[/TD]
[/TR]
</tbody>[/TABLE]

And 'final timetable' sheet that I want to look like this (no empty cells):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Harry[/TD]
[TD]Trumpet[/TD]
[TD]Wednesday[/TD]
[TD]10:30[/TD]
[/TR]
[TR]
[TD]Jackie[/TD]
[TD]Saxophone[/TD]
[TD]Wednesday[/TD]
[TD]11:30[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Flute[/TD]
[TD]Wednesday[/TD]
[TD]12noon[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]Trumpet[/TD]
[TD]Thursday[/TD]
[TD]10:30[/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td]Harry[/td][td]Trumpet[/td][td]Wednesday[/td][td]

10:30​

[/td][td][/td][td]

4​

[/td][td]

1​

[/td][td]Harry[/td][td]Trumpet[/td][td]Wednesday[/td][td]

0.4375​

[/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][td]Wednesday[/td][td]

11:00​

[/td][td][/td][td][/td][td]

3​

[/td][td]Jackie[/td][td]Saxophone[/td][td]Wednesday[/td][td]

0.479166667​

[/td][/tr]
[tr][td]

3​

[/td][td]Jackie[/td][td]Saxophone[/td][td]Wednesday[/td][td]

11:30​

[/td][td][/td][td][/td][td]

4​

[/td][td]Bob[/td][td]Flute[/td][td]Wednesday[/td][td]12noon[/td][/tr]
[tr][td]

4​

[/td][td]Bob[/td][td]Flute[/td][td]Wednesday[/td][td]12noon[/td][td][/td][td][/td][td]

6​

[/td][td]Jill[/td][td]Trumpet[/td][td]Thursday[/td][td]

0.4375​

[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Jill[/td][td]Trumpet[/td][td]Thursday[/td][td]

10:30​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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))))