Excel Data Sorting & Formatting

  • Thread starter Thread starter stuph
  • Start date Start date
S

stuph

I have a set of data with the form:

RECORDID1 RECORDID2 ONE TWO THREE FOUR FIELD MODIFIED FIVE SIX

There are 11 rows with the same data in RECORDID1, RECORDID2, ONE, TWO
THREE and FOUR.

FIELD is different for each row, but is repeated every 11 rows (so i
A3, FIELD will be Donkey, in A14, FIELD will be Donkey), MODIFIED, FIV
and SIX are different for each row

I need to take this data and put it all into a new worksheet with
different format. What I need to do is have one long row with all th
data sorted nicely for a Word Mail Merge Document...

So, I'm trying to get a worksheet that looks like:
RECORDID1 RECORDID2 ONE TWO THREE FOUR FIVE(ROW2) FIVE(ROW3
FIVE(ROW4) ... FIVE(ROW12) SIX(ROW2) SIX(ROW3) ... SIX(ROW12)

then the next line would be the next set of records, with the ne
RECORDID1, RECORDID2, etc...

this should continue until there is no more data (there is always dat
in RECORDID1, RECORDID2 fields, so there's no need to check for blan
spaces there)

I know I can just cut-and-paste w/ a transpose to get the same result
but since this something I'm going to have to do every day, and sinc
there are usually dozens of unique records every day, I'd really lik
to have it more automated than that.

Thanks for any help !! :
 
So your data consists of 12 like records (columns A:G) and varies in I:J???

And the data starts in row 3?

So 3-14 are a group, 15-26, 27-38, ....

But skip 3, 15, 27, ... when it gets transposed??

Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim myStep As Long
Dim myResize As Long

Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

myStep = 12
myResize = myStep - 1 '11 rows to copy|pastespecial transpose

oRow = 0
With curWks
FirstRow = 3
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow Step myStep
oRow = oRow + 1

'do columns A:f (1 row by 6 columns)
newWks.Cells(oRow, "A").Resize(1, 6).Value _
= .Cells(iRow, "A").Resize(1, 6).Value

.Cells(iRow, "I").Offset(1, 0).Resize(myResize).Copy
newWks.Cells(oRow, "G").PasteSpecial Transpose:=True

.Cells(iRow, "J").Offset(1, 0).Resize(myResize).Copy
newWks.Cells(oRow, "R").PasteSpecial _
Transpose:=True, Paste:=xlPasteValues
Next iRow

End With

newWks.UsedRange.Columns.AutoFit

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks for the help, but i actually finally plowed through the whol
thing.. but a couple things in there will help w/ stuff in the futur
:)

much appreciate
 
Back
Top