Urgent Help require regarding swapping.

  • Thread starter Thread starter Aligahk06
  • Start date Start date
A

Aligahk06

Dear All,
I want to fetch data from horizontal to vertical .
Data format is c6:j6 total eight values c7:j7 upto c21 to j21 total 128
values.
This 128 values which was placed horizontal i want to pull it into
vertical col.
i.e Range C6:J6 into D1:D8 and C7:J7 into D9:D16 and so on till range
C21to J21 into D... :D ... vertical.
Please assist either macro or Function.

Rgds,
aligahk06
 
Block the data you want to convert, click <Ctrl><C>, move to where you want
it, right click, and select Paste Special. Now select transpose, and it will
be pasted vertically.

--
HTH

Kassie

Replace xxx with hotmail
 
You can't do this directly in column D, as you will overwrite the
values that are there before you have chance to get them all. So, put
this formula in L1:

=INDEX(C:J,INT((ROW(A1)-1)/8)+6,MOD(ROW(A1)-1,8)+1)

Then copy this down to L128. Select all the cells in the range
L1:L128, click <copy>, then right-click and Paste Special | Values
(check) | OK then <Esc>. This will have fixed the values.

Then you can delete the columns you don't want, i.e. C to J, leaving
what was in column L now in column D.

Hope this helps.

Pete
 
Sir, Could u tell me how its working concept.

Pete_UK said:
You can't do this directly in column D, as you will overwrite the
values that are there before you have chance to get them all. So, put
this formula in L1:

=INDEX(C:J,INT((ROW(A1)-1)/8)+6,MOD(ROW(A1)-1,8)+1)

Then copy this down to L128. Select all the cells in the range
L1:L128, click <copy>, then right-click and Paste Special | Values
(check) | OK then <Esc>. This will have fixed the values.

Then you can delete the columns you don't want, i.e. C to J, leaving
what was in column L now in column D.

Hope this helps.

Pete
Click to expand...
 
Essentially, the formula is of the form:

=INDEX(block,row_to_get,column_to_get)

which will return a single cell from block, depending on the value of
the row and column parameters.

The block is defined as columns C to J, and because your data starts
in row 6 then as we copy the formula down we want to get data from
row6/col1, then row6/col2, then row6/col3 etc up to row6/col8, and
then row7/col1, row7/col2 etc.

The column term, MOD(ROW(A1)-1,8)+1, will initially return the value
given by MOD(1-1,8)+1, i.e. 0 + 1 , but as the formula is copied down
then ROW(A1) will become ROW(A2) returning 2, then ROW(A3) returning 3
etc, so the whole expression will return 1, 2, 3, 4, 5, 6, 7, 8, 1, 2,
3, etc on successive rows.

The row term in the INDEX formula, INT((ROW(A1)-1)/8)+6, will
initially return INT((1 - 1)/8)+6. i.e. 6, but on row 2 the expression
becomes INT((2-1)/8)+6, which will also return 6. It will keep
returning 6 up to the 8th row, as INT((8-1)/8)+6 will still give 6.
However, on the 9th row it will become INT((9-1)/8)+6, i.e.7, and will
continue to return 7 up to the 16th row. After that it will return 8
for a further 8 rows, then 9 for 8 rows, and so on.

Thus, as the formula is copied down it will bring the data from each
cell of the block in a sequential manner, taking the 8 cells on the
first row of the block, then the next 8 cells, and so on.

Hope this helps, and I hope it worked for you.

Pete
 
Back
Top