Removing Blanks (Array Formulas)

[ntlknight]

Hi there, I've been having a hard time successfully applying array formulas (that I've found online) to remove blanks. 5 of these formulas I found from bettersolutions and only 1 worked for me. I tried several other similar formulas from other sources, but none work quite as needed. In the Excel Sheet I'm attaching I've posted the 4 main formulas which seem like they should be working...but only one of them does work...and it's a formula that posts a #NUM! error in the empty cells below once the formula has run its course. So to get around this I created a "dummy" column of cells with the original formula, that prints all the correct info (including the #NUM!) and then point to those cells in a "final" column, the one I want to look at...using the IFERROR command to not display cells that have #NUM! in them. I believe the Sheet I'm attaching explains this more clearly. While I do now have a working version of what I need (using the 2nd set of "dummy" columns that makes the sheet bigger than it needs to be), after spending 4 hours last night trying to get the other formulas that don't print errors to work, I feel I should see this through properly till the end and understand what's happening a little better. Thank you so much for your time, I appreciate it!

[TMS]

N19:

Please Login or Register  to view this content.

Array entered and then copied down and across.

[ntlknight]

My friend, thank you so much for this help! Your formula works like a charm, even when copy/pasting it into my much bigger spreadsheet where the cells being calculated are separated by much bigger gaps in the columns and rows. It even ports over to Google Sheets seamlessly (which I use occasionally because of a projector system that is setup to a Chromebook. Google Sheets made the little adjustments to the formula automatically when I pasted them in. I am very grateful, thank you! (I guess I don't need to know exactly why or how it works!)

[TMS]

You're welcome.

I guess I don't need to know exactly why or how it works!

All I did was combine the two formulae you already had. That is, I put the IFERROR from the little formula in front of the big formula. Sometimes, it's easier to create a few helper columns to test parts of a formula and, when you're happy they do what you want them to do, you merge them together into a "super formula".



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.