need to transpose 3 or 4 columns to one column

[Rufus]

The matrix below represents what I need to do in Excel. I need to
transpose the data from row 1 of C1, C2, C3 and C4 to rows 1-4 of C5
then repeat for each additional row of C1-C4. I think I might need a
macro but I have no idea how to write it. Any help would be
appreciated. My data set is too large to utilize cut and paste
special. Thank you for your responses.

Rufus



C1 C2 C3 C4 C5
5 4 3 5 5
4 3 4 5 4
5 4 3 2 3
5
4
3
4
5
5
4
3
2

 

[Bernie Deitrick]

Rufus,

If your example table is entered in cells A14, enter this into cell E2, and copy down until it
returns #REF errors.

=INDEX($A$1:$D$4,INT((ROW()-1)/4)+2,IF(MOD((ROW()-1),4)=0,4,MOD((ROW()-1),4)))

HTH,
Bernie
MS Excel MVP

 

[Guest]

You can use the Transpose function (as apposed to the Copy Paste Special
version). Look on http://www.auditexcel.co.za/lookupandreference.html to see
how.