Excel columns to row

  • Thread starter Thread starter dr
  • Start date Start date
D

dr

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
 
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
Click to expand...


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
 
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
 
FWIW, IMHO,

You're not making a wise decision in revising your data to this type of
configuration!
 
If printing labels in Word from data in XL ... still a bad idea!

BUT ... each to his own.<g>
 
Back
Top