Converting Rows into Columns

  • Thread starter Thread starter Sheikh Saadi
  • Start date Start date
S

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
 
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
 
Thanks Pete, it just worked fine... also, i figure out this would also can do
the same task...

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

Hope this would help others...
 
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($D1:D1)-1+(ROWS($1:1)-1)*6,0)

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



Pete_UK said:
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.
Click to expand...
Click to expand...
 
Back
Top