This blog post from Feb 3, 2013 describes using an array formula to remove blanks from a list. I have modified the formula for rows instead of columns but it only works when the list begins in column A. I am needing the formulas to work when the list is in a column other than column A. The attached workbook shows my attempts so far. As you can see on row 1 I have list items in columns A-K with some blanks. The array formulas in columns M-W successfully remove the blank cells. On row 6 is my attempt to remove blanks in a list in columns O-Y. The formulas in columns AA-AK don't seem to work. Thank you in advance.
| https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range |
Hi John,
Welcome to the forum.
We use the COLUMN function to generate an array of values, in your case this COLUMN($A$2:$K$2) generates 1 through 11. You changed it to COLUMN($O$6:$Y$6) which generates an array of numbers 15 through 25, which isn't going to work because you want to get the values in the first 11 columns in your range O6:Y6, not columns 15 to 25 in your range...especially since you don't have that many columns 🙂
Your first formula also needs to retain COLUMN(A6), not COLUMN(O6).
This is what it should look like:
=IFERROR((INDEX($O$6:$Y$6,SMALL(IF(ISTEXT($O$6:$Y$6),COLUMN($A$2:$K$2)), COLUMN(A6)))),"")
Mynda
