convert vertical to horizontal

  • Thread starter Thread starter Mona
  • Start date Start date
M

Mona

I see lots of post but not one I can use. I am looking for a "function" that
will take vertical data to horizontal. I don't want the copy,paste,
transpose please.

Example :
1
2
3
Results
1 2 3

thanks!
 
Hi,

If your data start in a1 put this in a cell and drag right

=INDIRECT("A"&COLUMN(A1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
OP specifically said they didn't want to do that.

Assuming your data starts in A2:
=OFFSET($A$2,COLUMN(A1)-1,)

Copything this across will accomplish your goal.
 
TRANSPOSE is the easiest way, but if you don't want to use it, you can
use a formula like

=OFFSET($B3,COLUMN()-COLUMN($D$2),0,1,1)

Change $B3 to first cell of the existing data and change $D$2 to the
cell in which you enter this formula. Then copy this formula to the
right through as many columns as you have rows in the original data.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Data is in A1:A10

Select B1:K1 and enter =TRANSPOSE(A1:A10)

Hit CTRL + SHIFT + ENTER

But.............much easier using OFFSET or INDIRECT as others have posted.
 
Hi,

Say the cell reference of 1 is B5. In cell B10, type B5 (without the =
sign) and with the fill handle drag this to the right. When you do so, you
will get the cell references instead of values. Now select the array and
press Ctrl+H. In the Find box, type b and replacer with =b

Hope this helps.
 
Back
Top