# Compiling Data between Gaps

#### earthworm

##### Well-known Member
I have date like this

A1 : 500

A16 : 200

A17: 800

Since there are gaps between rows

what i want is , in column B I want to use the formula which can put the list in column B without putting the gaps in between

Example B1 : 500
B2 : 200
B3 : 800

without any gap , i have huge data , how can i achieve this ?

will this formula work ?

=IF(ROWS(B\$1:B1)>COUNTA(\$A\$1:\$A\$100),"",INDEX(\$A\$1:\$A\$100,SMALL(IF(\$A\$1:\$A\$100<>"",ROW(\$A\$1:\$A\$100)-ROW(\$A\$1)+1),ROWS(B\$1:B1))))

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### SteveO59L

##### Well-known Member
Why not Filter for non-blanks and copy/paste?

#### earthworm

##### Well-known Member
Why not Filter for non-blanks and copy/paste?

because i want the data to be robust . i dont want to do manual calculation

comon there must be some function like offset or something which can work

Bump....

#### al_b_cnu

##### Well-known Member

Hi, could you not just sort your data

#### kpark91

##### Well-known Member

when you mean gaps... do you mean blank rows?

exactly...

#### kpark91

##### Well-known Member
Since VBA is like the only thing I know...

Code:
``````Sub Test()
Dim count, last As Integer
count = 1

last = Range("A" & rows.Count).End(xlUp).Row

For c= 1 To last
If Range("A" & c) <> ""[B] Then[/B]
[B]              Range("B" & count) = Range("A" & c)[/B]
[B]              count = count + 1[/B]
[B]          End If[/B]
[B]     Next c[/B]
End Sub``````

Replies
3
Views
292
Replies
13
Views
399
Replies
6
Views
1K
Replies
4
Views
396
Replies
1
Views
299

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,611
Messages
5,832,694
Members
430,154
Latest member
Froggy16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back