recreate range with out blanks - help with formula

[Izhar]

Good morning folks,

i have range of data with some number information against it. i wish to recreate the same range with out any blanks

orginal range


range1 2014
AL 5
AU 6
CH
CO 7
EG 8
IN
IR 9
IR 10
KE
KU
ME 1
OM 1
PA 12


recreated range
range1 2014
AL 5
AU 6
CO 7
EG 8
IR 9
IR 10
ME 1
OM 1
PA 12

i want to do the abov with a formala without the blanks

thanks for your help,
Izhar

 

[rahulshewale1]

hii ,

try below formula


=IFERROR(INDEX($B$2:$B$14,AGGREGATE(15,6,(ROW($B$2:$B$14)-ROW($B$2)+1)/($B$2:$B$14<>""),ROWS($O$2:O2))),"")



Regard
Rahul shewale

 

[Izhar]

Dear Mr. Rahul,

this works like a dream, thank you for your help. Much appreciated.

Regards,
Izhar

 

[rahulshewale1]

Hii @Izhar ,

don't mention !


another option using power Query.

1.Go to Power Quary Tab
2.Select Table /Range
3.Click on B Column
4.Filter Uncheck " Null "


5.Close and Load