Data handeling

[shamshersadiq]

question.PNG

Hello everyone,

I have data as shown in attached image.
My input data is as shown in first column, i need in command in 2nd column such that it removes blank spaces and give me values as shown in column 2.

Thanks

[noboffinme]

Hi shamshersadiq,

I can't open the file you've posted, can you try to upload it again & then go to your post & see if you can open it.

Thanks

[Pete_UK]

You can use this array* formula in cell B2:

=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$12<>"",ROW($A$2:$A$12)),ROWS($1:1))),"")

then copy down.

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

Hope this helps.

Pete

[Pete_UK]

Thanks for the rep, shamshersadiq - I assume it's working OK for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

[shamshersadiq]

QS.PNG
I am not able to apply formula in F21?
Can you please help me?
Thanks

[FlameRetired]

Hi shamshersadiq.

Just change the address and row references in Pete's formula. Still array entered.

=IFERROR(INDEX($E:$E,SMALL(IF($E$21:$E$37<>"",ROW($A$21:$A$37)),ROWS($1:1))),"")