ExcelBanter - Excel columns to row

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Excel columns to row (https://www.excelbanter.com/new-users-excel/201643-excel-columns-row.html)

Excel columns to row

I have a spreadsheet with data arranged like:
Case ID+ Priority* Type* Item* Group+ Create Date

It is 75 Rows long.

I need to convert the data to look like the following:

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Excel columns to row

On Sun, 7 Sep 2008 08:28:01 -0700, dr
wrote:

I have a spreadsheet with data arranged like:
Case ID+ Priority* Type* Item* Group+ Create Date

It is 75 Rows long.

I need to convert the data to look like the following:

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Assuming your data is in columns A to F and on rows 1 to 75.
Try the following formula in cell G1:

=IF(MOD(ROW(),7)=0,"",INDEX($A$1:$F$75,1+INT(ROW()/7),MOD(ROW()-1,7)+1))

Copy down to row 525 (75*7)

Then copy column G and Paste Special (Values) it to column H
Finally delete columns A to G.

Hope this helps / Lars-Åke

Excel columns to row

Should be fairly quick. Takes col i:n and puts in col H. Just change to suit
making sure a clear column to the left.

Sub transposeblocks()
mc = 9 'column I
j = 1
lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = 1 To lr
Cells(i, mc).Resize(, 6).Copy
Cells(j, mc - 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
j = j + 7
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dr" wrote in message
...
I have a spreadsheet with data arranged like:
Case ID+ Priority* Type* Item* Group+ Create Date

It is 75 Rows long.

I need to convert the data to look like the following:

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Excel columns to row

FWIW, IMHO,

You're not making a wise decision in revising your data to this type of
configuration!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"dr" wrote in message
...
I have a spreadsheet with data arranged like:
Case ID+ Priority* Type* Item* Group+ Create Date

It is 75 Rows long.

I need to convert the data to look like the following:

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Case ID+
Priority*
Type*
Item*
Group+
Create Date

Excel columns to row