David, thanks for the tip but unfortunately it's not exactly what i wa
looking for. I thought i had been specific enough and i did reply t
tom prior to your posting. Maybe this will help clarify:

_Column_1_
Row 1 - Name
Row 2 - Address 1
Row 3 - Address 2
Row 4 - City, State Zip

_Column_2_
Row 1 - # pieces

_Column_3_
Row 1 - type of transaction (just one letter)

I am trying to sort in order to help route our trucks but keep th
groupings together

--
acarri
-----------------------------------------------------------------------
acarril's Profile: http://www.excelforum.com/member.php...fo&userid=1002
View this thread: http://www.excelforum.com/showthread.php?threadid=27741

Hi _____,
One row per address allows you to use
Mail Merge if you wanted to print labels. You will have a lot
more difficulty working in Excel if you try to maintain the data
on separate rows for a single address -- exactly as you are
experiencing.

I do not see a reply to Tom, and as I said Excel Forum does not
thread properly, among many other problems it causes.

Obviously this is not going to be a one time thing so you would
want to use a macro rather than the worksheet formulas, and
while it is important to get the data from the original source in
the form that you need it that seldom happens when PCs are
involved.

I created another example that better helps you with putting
your data into a single row. You should be able to record
a macro to sort the data and create another macro based
on this one to put it back to the original format if you have to
by reversing the assignments in the central part and
renaming the new proc to N7_511
Cells(cRow * 5 - 4, 1) = wsSource.Cells(cRow, 1)

Refer to web page for a graphical example.
This will create a new worksheet in your new format as the
last worksheet. If you want something else see my sheets.htm page.

Option Explicit
Public Sub N511_7()
'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie, 2004-11-19, programming
' http://www.mvps.org/dmcritchie/snake.htm#reformat
' A1, A2, A3, A4, A5, B1, C1 to 1st row
' A6, A7, A8, A9, A10, B6, C6 to 2nd row, etc.
Dim cRow As Long, newRows As Long, lastrow As Long
Dim wsSource As Worksheet
Dim wsNew As Worksheet
lastrow = Cells.SpecialCells(xlLastCell).Row
newRows = Int((lastrow + 4) / 5)
Set wsSource = ActiveSheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
Set wsNew = ActiveSheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For cRow = 1 To newRows
Cells(cRow, 1) = wsSource.Cells(cRow * 5 - 4, 1)
Cells(cRow, 2) = wsSource.Cells(cRow * 5 - 3, 1)
Cells(cRow, 3) = wsSource.Cells(cRow * 5 - 2, 1)
Cells(cRow, 4) = wsSource.Cells(cRow * 5 - 1, 1)
Cells(cRow, 5) = wsSource.Cells(cRow * 5 - 0, 1)
Cells(cRow, 6) = wsSource.Cells(cRow * 5 - 4, 2)
Cells(cRow, 7) = wsSource.Cells(cRow * 5 - 4, 3)
Next cRow
Cells.EntireColumn.AutoFit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

A link to directions in on the same web page, but you will find it on
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"acarril" wrote in message ...

David, thanks for the tip but unfortunately it's not exactly what i was
looking for. I thought i had been specific enough and i did reply to
tom prior to your posting. Maybe this will help clarify:

_Column_1_
Row 1 - Name
Row 2 - Address 1
Row 3 - Address 2
Row 4 - City, State Zip

_Column_2_
Row 1 - # pieces

_Column_3_
Row 1 - type of transaction (just one letter)

I am trying to sort in order to help route our trucks but keep the
groupings together.