# Combining two columns into a single columns eliminating blanks

1. ## Combining two columns into a single columns eliminating blanks

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?

2. ## Re: Combining two columns into a single columns eliminating blanks

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

3. ## Re: Combining two columns into a single columns eliminating blanks

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1