Rearranging DATA

[raj74]

I have a list of data written in col. say 105 no data in A1:A105

Now I wart to write those 105 data in rows, Each row can have maximun 10
data.The next data (11th) will go to next row. for example wehave data

A1=1
A2 =2
A3=13
A4 =12
A5=14
....
....
...
...
....
A105= 25

i want to rearrange those data in the following way

say, C1 D1 ..................................... L1 (total 10 data in
each row)
C2 D2 L2
and so on...

Where C1 = A1, D1 = A2,..........................L1= A10
C2=A11, D2 =A12..........................L2=A20
......................

Hope I can explain properly,

Thanks

 

[Francis]

try

Select B1:J1 and enter this array formula
=TRANSPOSE(A2:A9)

confirm by Ctrl, Shift and Enter
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Francis]

another way is using the Edit | Paste Sepcial |Transpose feature:

1. Select and copy the data you want to transpose
2. Position the cell pointer in the first destination cell
3. Select Edit | Paste Special | Transpose
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[raj74]

Hello Francis!!!!!!

Your 1st Solution:
Selecting a range (B1:J1) (where exactly the first 9 value (A1:A9) to be
pasted) and then writing transpose (A2:A9) gives the first value that is B1
to be correct, not the other values.
Your second solution:
If my input consists of 1000 data, I will prefer easiser solution than 100
times using the commend (paste special, transpose) you mentioned to create
100 rows, each rows having 10 data maximum.
Best solution will be create 1st row of 10 data and drag the rows to get the
other rows. That solution I am looking for,
Thanks anyway.

Regards

 

[Francis]

Hi raj

The first formula provided did transposed the data from column to row
I bet you didn't place the formula into the formula bar and confirm by
Ctrl, Shift and Enter all at the same time as this is an array formula.
But you need to change the cell's references for every rows in the formula
which you said you may not want.

Try this formula in B1 and copy it to K1, then drag it down
=INDEX($A:$A,(ROW()-1)*10-10+COLUMNS($A:J)+1)

You may hide/delete Col A after you finish

Remeber to click the Yes button below
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[raj74]

Hi Francis!!!!!!

Sorry for the late reply, I went for night sleep.
Thanks very much for you r reply.
But onething, I can not delete the input colm, can only hide this, othewise
formula will be disturbed. Or I can copy the output cell and repaste in the
same location with only values. Then I can delete the input col. You probably
agree.

Thanks very much.

Regards

raj

 

[Francis]

Hi raj

If you can't delete it then you may may to hide it or paste as value to
another
sheet. Am glad that this works for you.

Do us a favor, can take a moment to click the Yes button below, this
will allow others to serach the archive easlier in case of the same issue
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another