earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 759
- 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))))
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))))