Combining two columns into a single columns eliminating blanks

**khank** · 01-19-2011, 04:24 PM

I've attached a sample.

As you'll see I've applied a formula in Column V and arrayed down.

{=IFERROR(IFERROR(INDEX($K$9:$K$59,SMALL(IF($K$9:$K$59<>"",ROW($K$9:$K$59)-ROW($K$9)+1),ROWS($K$1:$K1))),INDEX($L$9:$L$59,SMALL(IF($L$9:$L$59<>"",ROW($L$9:$L$59)-ROW($L$9)+1),ROWS($L$1:$L1)-COUNTIF($K$9:$K$59,"<>")))),"")}

It is picking up Column K then there's 14 blank rows then Column L appears, the 14 blanks are from the first 14 rows before a Column L number appears.

How can I eliminate all blanks and zero's???? and have one continuous Column V of numbers?

**NBVC** · 01-19-2011, 04:43 PM

First, to keep column K cleaner and getting rid of the 0's, try replacing formula in K9 with:

Please Login or Register  to view this content.

Then, as we discussed in a previous thread, because you have formula nulls we need to replace this part of the formula in column V: COUNTIF($K$9:$K$59,"<>") with COUNTIF($K$9:$K$59,"?*")

so the formula is now:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

**DonkeyOte** · 01-19-2011, 06:16 PM

If the ordering of the numbers is not important (ie retrieve in order) then you can leave K as is if you wish and use the below:

Please Login or Register  to view this content.

of course the above is designed specifically with numbers in mind - per all of your prior threads on this topic (of which there are seemingly many)

Combining two columns into a single columns eliminating blanks

LinkBack

Thread Tools

Rate This Thread

Display

Combining two columns into a single columns eliminating blanks

Re: Combining two columns into a single columns eliminating blanks

Re: Combining two columns into a single columns eliminating blanks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions