How to sort when info is grouped in rows?

Hi,
I have a .csv file that I've opened in excel - the data is grouped so that column A contains groups of data, for example, row 1 will be the name, row 2 will be address, 3 is city, 4 is province, 5 is postal code. It then has a couple of blank rows, then some useless info for the next 3 rows, then starts back with the name, address grouping again.

I'm trying to figure out how to sort it so that the info in the row becomes sorted into columns
i.e. column for name, address, city, etc and eliminate the blank and useless rows.
Can someone tell me how to do this - having much fun getting this file in a format that can be imported into another program. Wouldn't be a problem to manually enter but there are over 1000 groupings with the name & address info.

Any help would be much appreciated - sorry, I'm pretty new to this data sorting so I am really looking for a solution.
Thanks for any help you can provide.

hi LaVerne,

Sort is not going to change your listing from a mailing labels
format to columns. Perhaps you meant for that word to be
rearrange.

You can accomplish this with worksheet functions and use
of the fill handle,
http://www.mvps.org/dmcritchie/excel...ol.htm#snkAddr
http://www.mvps.org/dmcritchie/excel/fillhand.htm

You have 10 rows, so the formula in your second sheet would
be as follows in cell A1, then use the fill handle to the right
and to the left. Normally you would delete the unwanted
columns but since they are at the end you only have to
fill to the fifth column (column E).
A1: =OFFSET(contacts!$A$1,ROW()*10-11+COLUMN(),0)&""

If you prefer a macro solution:
http://www.mvps.org/dmcritchie/excel...l.htm#reformat

---
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

"LaVerne" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
> I have a .csv file that I've opened in excel - the data is grouped so
> that column A contains groups of data, for example, row 1 will be the
> name, row 2 will be address, 3 is city, 4 is province, 5 is postal
> code. It then has a couple of blank rows, then some useless info for
> the next 3 rows, then starts back with the name, address grouping
> again.
>
> I'm trying to figure out how to sort it so that the info in the row
> becomes sorted into columns
> i.e. column for name, address, city, etc and eliminate the blank and
> useless rows.
> Can someone tell me how to do this - having much fun getting this file
> in a format that can be imported into another program. Wouldn't be a
> problem to manually enter but there are over 1000 groupings with the
> name & address info.
>
> Any help would be much appreciated - sorry, I'm pretty new to this data
> sorting so I am really looking for a solution.
> Thanks for any help you can provide.
>
>
> --
> LaVerne
> ------------------------------------------------------------------------
> LaVerne's Profile: http://www.excelforum.com/member.php...o&userid=24360
> View this thread: http://www.excelforum.com/showthread...hreadid=379576
>

Just to clarify - I copy this formula into cell A1 of the second page of this workbook, and then on first page of workbook I do the fill handle down to bottom
of the data (then what, copy to cell A2?). Sorry - definitely not something I'm familiar with so I'm not sure what you are describing. Please excuse my inexperience!

As it turns out, there are 3or 4 columns that actually have some data in them which I need (they are consecutive, like A-D). There's another complication, in that each of the records doesn't necessarily have 10 rows exactly - in some cases they have 9, or maybe even 11.

I'm seeing some serious cut-paste in my future (blech). I appreciate your suggestion, but does this makes it impossible to rearrange this info without doing it manually cell by cell, row by row?..........sounds awful doesn't it.

Hi LaVerne,
There is no Cut & Paste involved. There is no Copy & Paste involved.

The formula to be placed in cell A1 of second sheet beginning at the equal sign:
=OFFSET(contacts!$A$1,ROW()*10-11+COLUMN(),0)&""
included contacts as the sheetname of the first sheet. Those are
two double quotes at the end of the formula they enclose an empty
string to ensure you get a character instead of a zero from an empty
cell.

In sheet2 place the following formula referring back to sheet1
=OFFSET(sheet1!$A$1,ROW()*10-11+COLUMN(),0)&""

Grab the fill handle on sheet2 cell A1 and drag it to the right through
cell E1, then with cells A1:E1 still selected drag the fill handle down
as far as needed to get all data from first sheet that was wanted.

You could drag A1 through J1 when starting above so that you can
visually check that you weren't interested in the last five columns.

When finished, on the second sheet enter Ctrl+A to select all cells
then use Edit, Paste Special, Values to eliminate all formulas
so that you can sort data as wanted.

Disclaimer: If using Excel 2003 you have to use Ctrl+Shift+Spacebar
instead of Ctrl+A Dropping conventional use of Ctrl+A is contrary to
any other PC application including IE and other Office products.

If the first sheetname included spaces you would enclose it's sheetname
within single quotes
=OFFSET('sheet one'!$A$1,ROW()*10-11+COLUMN(),0)&""

If you have suggestions for making my referenced material more
readable please send me email.
---
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

"LaVerne" <[email protected]> wrote in message
news:[email protected]...
>
> Just to clarify - I copy this formula into cell A1 of the second page of
> this workbook, and then on first page of workbook I do the fill handle
> down to bottom
> of the data (then what, copy to cell A2?). Sorry - definitely not
> something I'm familiar with so I'm not sure what you are describing.
> Please excuse my inexperience!
>
> As it turns out, there are 3or 4 columns that actually have some data
> in them which I need (they are consecutive, like A-D). There's another
> complication, in that each of the records doesn't necessarily have 10
> rows exactly - in some cases they have 9, or maybe even 11.
>
> I'm seeing some serious cut-paste in my future (blech). I appreciate
> your suggestion, but does this makes it impossible to rearrange this
> info without doing it manually cell by cell, row by
> row?..........sounds awful doesn't it.

Hi LaVerne,

You will have to use a macro

Option Explicit

Sub Macro15()
Dim i As Long, j As Long
Columns("A:A").Select
repeater:
On Error Resume Next
Selection.Find(What:="" & Chr(10) & "", _
After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
ActiveCell.Offset(1, 0).EntireRow.Insert
i = InStr(1, ActiveCell.Value, Chr(10), 0)
If i <> 0 Then
ActiveCell.Offset(1, 0) = Mid(ActiveCell.Value, i + 1)
ActiveCell = Left(ActiveCell.Value, i - 1)
j = InStr(1, ActiveCell.Offset(0, 1).Value, Chr(10), 0)
If j <> 0 Then
ActiveCell.Offset(1, 1) = Mid(ActiveCell.Offset(0, 1).Value, j + 1)
ActiveCell.Offset(0, 1) = Left(ActiveCell.Offset(0, 1).Value, j - 1)
End If
End If
GoTo repeater
End Sub


instructions for installing and using a macro in
ttp://www.mvps.org/dmcritchie/excel/getstarted.htm

The above macro changes the selection so is not written
very efficiently. But it will split up column A cells that
if finds CHAR(10) which is a Line-Feed (LF) and when it
finds one in Column A it will check the corresponding cell
in column B and do the same.

---
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

"LaVerne" <[email protected]> wrote in message
news:[email protected]...
>
> That's incredible. Thank you so much - I can't get over how much this
> program is capable of.
>
> Here's a new twist - different file, also .csv. This time 2 columns of
> data, grouped in blocks of 10 as before, but here's the twist - in cell
> A2 there are multiple items so it looks like what I've entered below -
> no commas or anything at the end of each line. Each of the other cells
> that are part of the block in that column only have a single item in
> them. The cells in column B (rows 1-10) only have single values in
> them too. I'd love to know if you have a formula for this one - this
> is also a big file and I'd certainly love to be able to get the data
> into columns with a formula rather than manually editing each entry.
> Your input is really appreciated - thanks again.
>
> Cell A1 -
> Group
>
> Cell A2 -
> Account code
> Full account name
> Location
> File #
>
> Cell A3 -
> Salesperson
>
> Cell A4 -
> Department
>
> Cell A5 -
> Notes
>
>
> --
> LaVerne
> ------------------------------------------------------------------------
> LaVerne's Profile: http://www.excelforum.com/member.php...o&userid=24360
> View this thread: http://www.excelforum.com/showthread...hreadid=379576
>