How to sort when info is grouped in rows?

[LaVerne]

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.

 

[David McRitchie]

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/snakecol.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/snakecol.htm#reformat

 

[LaVerne]

Just to clarify - I copy this formula into cell A1 of the second page o
this workbook, and then on first page of workbook I do the fill handl
down to bottom
of the data (then what, copy to cell A2?). Sorry - definitely no
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 dat
in them which I need (they are consecutive, like A-D). There's anothe
complication, in that each of the records doesn't necessarily have 1
rows exactly - in some cases they have 9, or maybe even 11.

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

 

[David McRitchie]

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.

 

[LaVerne]

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

 

[David McRitchie]

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.