Compiling Data between Gaps

earthworm

Well-known Member
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526

ADVERTISEMENT

Hi, could you not just sort your data
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582

ADVERTISEMENT

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

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top