CODEWITHSUNDEEP
excelexcel-formula
user10547454
user10547454

Reputation: 1

Index Match - Removing Blanks From Table Column

This is what was posted from the formula sorting the numbers from the blanks

The original table columns with blanks

I don't know why it has done this for this one column, its worked on every other column in the table except for this one. Just curious as to why excel isn't completing the formula properly. 

{=IFERROR(INDEX(Q137:Q151,SMALL(IF(Q137:Q151<>"",ROW(Q137:Q151)-ROW(Q136)),ROW()-ROW(Q136))),"")}

Upvotes: 0

Views: 382

Answers (1)

user10547454
user10547454

Reputation: 1

Turns out I'm an idiot and had a space in the cell all along, I apologize....

The formula:

{=IFERROR(INDEX(N137:N151,SMALL(IF(N137:N151<>"",ROW(N137:N151)-ROW(N136)),ROW()-ROW(N136))),"")}

(with the "N136" always being the cell right before your actual list of data begins)

Works as an amazing function to remove blank cells from a row in order to expand into other features such as a Dynamic Named Range, which is great for graphs and a automatic data validation list.

Upvotes: 0

Related Questions