outline/group (97)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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:

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

Select the relevant range in column A (ie – all the cells that have address values in column 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 much better alternative is to put all the required information onto one line. Put the following formulas in your s/sheet without the quotation marks:

in C1:  "=b2"
in D1:  "=b3"

then select the range C1:D3 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!

Reply | Quote

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.

Reply | Quote