Compiling Data between Gaps

earthworm

Active Member
Joined
May 19, 2009
Messages
347
Office Version
2019, 2016
Platform
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))))
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

earthworm

Active Member
Joined
May 19, 2009
Messages
347
Office Version
2019, 2016
Platform
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,494

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,865
Messages
5,513,866
Members
408,974
Latest member
dregsy

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top