Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    outline/group (97)

    I want to sort a list of names & addresses by surname. The data is laid out as follows: surname1is in a1,address in b1,b2,b3 surname2 in a4, address in b4,b5,b6. etc.
    So basically I want to sort by column a, but keeping the addresses in col b attached to the correct surnames.
    Been playing around with group/outline, but I don't think the solution lies there.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: outline/group (97)

    cormac9

    To sort this kind of list properly you need to have something in the empty cells (A2 and A3 and B5 and B6) in your example.

    I suggest you use a separate column with a series of numbers in 3s. Something like a series 1,1,1,2,2,2,3,3,3... and sort by that column to keep all the addresses together and using the A Column to sort alphabetically.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: outline/group (97)

    I can see two choices. If you sort on the file as it sits now the blank cells in column A (A2:A3, A5:A6, etc) will all group together in the sort - and the data in the coresponding cells in column B will no longer be associated with the correct name in column A. The technical term is unprintable in a family forum, but it will not be fun! (been there, incompetent clerical help has done that!).

    You can either associate each of those cells with the correct name by copying the name information to the appropriate cells in column A, or you could put all the information in one row by moving the second and third address lines to the same row as the name and first line of the address. I recommend the latter, but lets deal with both techniques

    To do the first one, you can use a little VBA routine as follows:
    <pre>Sub FillBlank()
    Dim Cell As Range

    For Each Cell In Selection
    If Cell.Formula = "" Then Cell.Value = Cell.Offset(-1, 0).Value
    Next Cell

    End Sub
    </pre>


    Select the relevant range in column A (ie - all the cells that have address values in column [img]/forums/images/smilies/cool.gif[/img] and run the macro - it steps through the selection and drops the value found in the cell immediately above into any blank cell. In my opinion this looks horrible, but at least you will be able to sort the file and get meanigful results.

    A <font color=red>much</font color=red> better alternative is to put all the required information onto one line. Put the following formulas in your s/sheet without the quotation marks:
    <pre>in C1: "=b2"
    in D1: "=b3"
    </pre>

    then select the range C13 and copy it down the length of active cells in column B. Every fourth row in column C and D will have a formula placing the address information on a single row, with blanks in the intervening rows. Copy and then Paste Special | Values to transform the formulas into string values. Finally, if you sort the file on column A all the rows with blanks in column A will be sorted together. They are now redundant, and can be deleted. You will have names and addresses sortable by name (or by city, post code, etc if the address lines were normally laid out).

    The advantage of this approach is that this format is much easier to use as a mailmerge source for Word - which is likely where you will want to use it in any event!

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: outline/group (97)

    There are a few ways to go, and if you need to maintain this format, VBA may be best. However, the attached file shows a quick-&-dirty, one-time change to restructure your data into pure columns, after which the sorting is easy.

    Sheet1 is how I understand your data is now laid out, Sheet2 restructures it using formulas, Sheet3 takes the formulas and copies the data over the formulas by using Edit, Paste Special, Values.

    All that remains to be done is sorting the entire data, and then you ignore the "left over" address fields in column B.

    If this won't do it, let us know what you need.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •