#### earthworm

##### Active Member

- Joined
- May 19, 2009

- Messages
- 347

- Office Version
- 2019, 2016

- Platform
- Windows

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