Converting Rows into Columns

[Sheikh Saadi]

Hi All,

I have an excel sheet, having data (Customer Records) in column A. The data
have 5 rows for for each customer. I want to convert 5 Rows data into 1 Row
data with 5 different columns. (After every 5th Row, new records starts). I
think it could be don with Index, but can't figure out how. Plz help...


The original data:

Col A
----------
ABC
Hard Disk
4
$50
$200
DEF
LCD
2
$150
$300
GHI
Printer
1
$175
$175
--------------------

will become:

A B C D E

 

[Pete_UK]

Assuming your data is in column A starting with A1, then you can put
this for example in C1:

=INDEX($A:$A,(ROW(A1)-1)*5+COLUMN(A1))

Then copy it across into D1:G1, and then copy C1:G1 down as far as you
need to - if you copy it too far you will get a row of zeros.

Fix the values then you can get rid of columns A and B.

Hope this helps.

Pete

 

[Sheikh Saadi]

Thanks Pete, it just worked fine... also, i figure out this would also can do
the same task...

=OFFSET($D$1,COLUMNS($D11)-1+(ROWS($1:1)-1)*6,0)

Hope this would help others...

 

[Pete_UK]

Thanks for feeding back.

It could also be done with INDIRECT, but it (and OFFSET) are volatile
functions which can impair the performance of your workbook.

Pete

Thanks Pete, it just worked fine... also, i figure out this would also can do
the same task...

=OFFSET($D$1,COLUMNS($D11)-1+(ROWS($1:1)-1)*6,0)

Hope this would help others...
--
Sheikh Saadi

Assuming your data is in column A starting with A1, then you can put
this for example in C1:

Then copy it across into D1:G1, and then copy C1:G1 down as far as you
need to - if you copy it too far you will get a row of zeros.