Compiling Data between Gaps

[earthworm]

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

 

[SteveO59L]

Why not Filter for non-blanks and copy/paste?

 

[earthworm]

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

 

[earthworm]

Bump....

 

[al_b_cnu]

Hi, could you not just sort your data

 

[earthworm]

Hi, could you not just sort your data

no

 

[kpark91]

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

 

[earthworm]

exactly...

 

[kpark91]

Since VBA is like the only thing I know...

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