# How to convert a horizontal vector with blanks into a vertical one without blanks

1. ## How to convert a horizontal vector with blanks into a vertical one without blanks

I have a row that is 400 columns wide with values sporadically thrown around in it. Majority of the values are blanks. Is there a way, without using visual basic, to put these nicely into a single column without blanks, using formulas only? There will never be more than 50 non-blank values, thus no more than 50 cells with formulas. I attached a sample file so it's a bit easier to understand what I am after. Thank you!
Horizontal to Vertical Example.xlsx

2. ## Re: How to convert a horizontal vector with blanks into a vertical one without blanks

copy the row,
paste transposed
sort
(record it as macro)

3. ## Re: How to convert a horizontal vector with blanks into a vertical one without blanks

I wanted to see if I can do it without VB - formulas only.

4. ## Re: How to convert a horizontal vector with blanks into a vertical one without blanks

Try this...

This array formula** entered in B9:

=IFERROR(INDEX(\$3:\$3,SMALL(IF(D\$3:OM\$3<>"",COLUMN(D\$3:OM\$3)),ROWS(B\$9:B9))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

5. ## Re: How to convert a horizontal vector with blanks into a vertical one without blanks

Awesome! That works perfectly. Thank you.

6. ## Re: How to convert a horizontal vector with blanks into a vertical one without blanks

You're welcome. Thanks for the feedback!

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