Change formula that removes blanks in columns to rows

[Big.Moe]

I have found the formula below and have used it successfully to remove blanks in vertical ranges (columns). However, I now need that same formula to remove blanks in horizontal ranges (rows).

Please Login or Register  to view this content.

At the risk of the forum gurus chuckling at my rudimentary attempt, I have tried to adapt the formula to the attached sheet ("JAN" tab) and change the ranges of the formula to rows and used the "COLUMN" function instead of the "ROW" function.

Please Login or Register  to view this content.

Unfortunately, the core logic of the original formula is too complex and still escapes me, so I am going at it a little blindly. Am I going in the right direction, or is there another formula I should be looking at?

[jeffreybrown]

Hi Big.Moe,

If I understand what you are attempting, you want to return the numbers in row 8 starting at column X and to the right! If this is correct.

=INDEX($X$8:$BH$8,SMALL(IF($X$8:$BH$8>0,COLUMN($X$8:$BH$8)-COLUMN($X$8)+1),COLUMNS($T$8:T8)))

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

[Big.Moe]

WOW! Thanks you so much Jeffrey. That was exactly it. I'd love to think I would of come up with that solution eventually, but I'm afraid my Excel chops aren't quite up to the task.....but I keep trying.

[jeffreybrown]

but I keep trying.

And that's what it all about.

You are very welcome and thanks for the rep/feedback.

[Big.Moe]

Quick question: I changed the target cells to have numbers and text (i.e. 22VWR rather than just 22). The formula works well for the first non-blank cell, but returns nothing for the next non-blank cells. Is there a way to change the formula to accept this change?

Edit #1: Hmmm, looks like the problem was not the change of values. The problem only occurs in the next cell using your formula.
Edit #2: If I delete the equations in the target cells, then your formula works in all four summary cells. When I put the formulas back in, results go away. rang
Edit #3: Solved the problem. Had to change the >0 to <>"".