Newbie here. I have a spreadsheet with raw data that is organized in a
fashion that I cannot sort. Each record takes up multiple lines, with the
record identifier only in cell A2. I want to move the data around so it is
all on one row. For example, the first record takes up (A2:c:4). I want to
be able to make A1 the first column of the first record, and move everything
from A2:c5 to be on row 1.
ie: raw data:
My_A2 My_B2 My_C2
My_A3 My_B3 My_C3
My_A4 My_B4 My_C4
I want:
My_A2 My_B2 My_C2 My_A3 My_B3 My_C3 My_A4 My_B4 My_C4

I want to be able to do this for each row all the way down the spreadsheet
for a given range (My_Range), fields A3 & A4 will always be blank, so I can
resort and save only the rows where A2 has data.

Please advise if any one out there has a great idea how to do this!

~~ L J
Lori Jo Vincent
Using: Excel 2003, WindowsXP Professional

A technique that I have used in the past when I didn't want to spend the
time writing a macro to do this sort of thing (although I'll admit this
is a fairly common problem for Excel users!):

I would leave any column labels on row 1, then start the data on row 2,
like so:

Label1 Label2 Label3 Label4 Label5 Label6 Label7
Label8 Label9
My_A2 My_B2 My_C2 My_A3 My_B3 My_C3 My_A4 My_B4 My_C4

Format the row of labels as Bold, with cell underline. Now you have a
list.

Then use worksheet formulas to copy row 3 up to columns D thru F, and
row 4 up to columns G thru I.
Include logic in the formulas so that columns D thru I on rows 4 and 5
are empty strings.
Copy and paste special all values.
Include an extra column for the purpose of numbering each row of data,
if the original order is to be maintained.
Sort the list by any column D thru I. This will force all of the empty
strings to go to the bottom or top of the list.
Delete the rows with blank data in columns D thru I.
Re-sort the list and then delete by the numbered column, if necessary.

If you would rather write a macro, then you will have to specify how to
tell when a new record begins, if there might be any blank rows or other
"non-data" anywhere in the worksheet. Is your data always fixed (3 rows,
with absolutely no blank data or other "garbage" anywhere on the
worksheet)?
--
Regards,
Bill Renaud

Another possiblity: Hopefully an MVP can suggest a pre-written macro
from their library somewhere. I just don't happen to know of one at the
moment.
--
Regards,
Bill Renaud

This sounds very time consuming, that I would have to do each row at a time.
I have 75 spreadsheets with 200 rows each that I need to eventually drop into
Access. I was hoping somebody out there had some sort of For..Next that
would loop to the end of the range.

"Bill Renaud" wrote:

A technique that I have used in the past when I didn't want to spend the
time writing a macro to do this sort of thing (although I'll admit this
is a fairly common problem for Excel users!):

I would leave any column labels on row 1, then start the data on row 2,
like so:

Label1 Label2 Label3 Label4 Label5 Label6 Label7
Label8 Label9
My_A2 My_B2 My_C2 My_A3 My_B3 My_C3 My_A4 My_B4 My_C4

Format the row of labels as Bold, with cell underline. Now you have a
list.

Then use worksheet formulas to copy row 3 up to columns D thru F, and
row 4 up to columns G thru I.
Include logic in the formulas so that columns D thru I on rows 4 and 5
are empty strings.
Copy and paste special all values.
Include an extra column for the purpose of numbering each row of data,
if the original order is to be maintained.
Sort the list by any column D thru I. This will force all of the empty
strings to go to the bottom or top of the list.
Delete the rows with blank data in columns D thru I.
Re-sort the list and then delete by the numbered column, if necessary.

If you would rather write a macro, then you will have to specify how to
tell when a new record begins, if there might be any blank rows or other
"non-data" anywhere in the worksheet. Is your data always fixed (3 rows,
with absolutely no blank data or other "garbage" anywhere on the
worksheet)?
--
Regards,
Bill Renaud

I didn't realize you had so much data. Normally, we don't do free
consulting in this newsgroup, but try the following code:
Paste this into a standard code module (in the VBA editor, choose
Insert|Module).
It adds a new worksheet to your workbook, then copies all of the data
over to the new worksheet, so your original data remains intact.
Change the Field names in cells D1:I1 to match your actual data. With
more input, I could add this to the code for you.

'----------------------------------------------------------------------
Public Sub UnwrapData()
Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim lngBlocksToCopy As Long
Dim lngBlockIndex As Long
Dim rngSource As Range
Dim rngDest As Range

Application.ScreenUpdating = False

Set wsData = ActiveSheet
Set wsNew = ActiveWorkbook.Worksheets.Add

wsNew.Move After:=wsData

'Copy column labels to new worksheet.
CopyBlock wsData.Range("A1:C1"), wsNew.Range("A1")
With wsNew.Range("D1")
.Formula = "Field4"
.AutoFill Destination:=.Resize(1, 6), Type:=xlFillDefault
End With

lngBlocksToCopy = WorksheetFunction _
.RoundUp((wsData.UsedRange.Rows.Count - 1) / 3, 0)

'Set up pointers to ranges.
Set rngSource = wsData.Range("A2:C2")
Set rngDest = wsNew.Range("A2")

For lngBlockIndex = 1 To lngBlocksToCopy
CopyBlock rngSource, rngDest
CopyBlock rngSource.Offset(1), rngDest.Offset(0, 3)
CopyBlock rngSource.Offset(2), rngDest.Offset(0, 6)

'Move the pointers to ranges to next block.
Set rngSource = rngSource.Offset(3)
Set rngDest = rngDest.Offset(1)
Next lngBlockIndex

'Format header cells on new worksheet.
With wsNew.Range("A1:I1")
.Font.Bold = True
.Borders(xlEdgeBottom).Weight = xlThin
End With

'Show new worksheet.
With wsNew
.Activate
.Range("A1").Activate
End With
End Sub

'----------------------------------------------------------------------
Public Sub CopyBlock(rngSource As Range, rngDest As Range)
rngSource.Copy
rngDest.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

--
Regards,
Bill Renaud

You need to have the worksheet that contains the data to be unwrapped
active when you start the macro.
It will automatically insert a new worksheet, find all of the data areas
and copy the data accordingly.

I would put the code into a new, blank workbook, then attach a new
toolbar to it that has a button (smiley face or something) on it. Then
hide the workbook and save from the VBA editor. Then you can use the
macro an any data workbook that is currently active (all 75 of them!).

Reply to the newsgroup, if you need more detailed instructions.
--
Regards,
Bill Renaud