How do I sort this list?
Martin 忽帕
above and together with their paintings, medium and price.
This is only a sample of the list which takes about 7 pages A4
The list always starts at number 1 but the total number of paintings
will vary from exhibition to exhibition.
Using XL 2000
Any help to get this sorted greatly appreciated
Martin
©¿©¬
removethis to email
========================================================
NO. MEDIUM PRICE
Fleck Margaret
106 NASTURTIUM Watercolour £90
105 TULIPS Watercolour £95
104 RED BAY - WATERFOOT Pastel £100
107 BLUEBELLS Watercolour £58
Fleck William
109 DONEGAL Watercolour £75
108 FARM HOUSE - GLENS OF ANTRIM Watercolour £75
110 A "GREY DAY" Watercolour £55
Gawn May
57 AN APPLE A DAY Watercolour £80
54 COTTAGE GARDEN Watercolour £70
55 SUNSHINE AND SHADOW (1) Watercolour £48
56 SUNSHINE AND SHADOW (2) Watercolour £48
Gingles Thora
85 ANYBODY HOME? OIL £60
84 POPPIES Watercolour £50
83 ROSE OIL £45
Bones Caroline
87 AT SEA Watercolour £50
88 COTTAGE IN ACHILL ISLAND Watercolour £55
86 DONEGALL COTTAGE Watercolour £40
91 CALM WATERS Watercolour £55
90 GALWAY COAST Watercolour £55
89 A SEASIDE COTTAGE Watercolour £55
Robert Rosenberg
individually. I suggest you create a more columnar list, with columns for
Painter, Paint Name, Paint Type, etc. See my converted example below. This
converted example can be sorted easily by Painter, then by Number. You can
also use either the Subtotals feature or the Outlining feature to group the
list by painter, achieving close to the same effect you currently have with
your version of the list.
PAINTER PAINT NAME PAINT TYPE PRICE
NUMBER
Fleck Margaret NASTURTIUM Watercolour Ł90
106
Fleck Margaret TULIPS Watercolour
Ł95 105
Fleck Margaret RED BAY WATERFOOT Pastel Ł100
104
Fleck Margaret BLUEBELLS Watercolour
Ł58 107
.
.
.
--
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
"Martin ©ż©¬" <mar...@martinmcd.fsnet.co.uk> wrote in message
news:r6foqugdvahhli4ts...@4ax.com...
> How do I sort this list into number order keeping the artist's name
> above and together with their paintings, medium and price.
> This is only a sample of the list which takes about 7 pages A4
> The list always starts at number 1 but the total number of paintings
> will vary from exhibition to exhibition.
> Using XL 2000
> Any help to get this sorted greatly appreciated
> Martin
> ©ż©¬
> removethis to email
> ========================================================
> NO. MEDIUM PRICE
> Fleck Margaret
> 106 NASTURTIUM Watercolour Ł90
> 105 TULIPS Watercolour Ł95
> 104 RED BAY - WATERFOOT Pastel Ł100
> 107 BLUEBELLS Watercolour Ł58
>
> Fleck William
> 109 DONEGAL Watercolour Ł75
> 108 FARM HOUSE - GLENS OF ANTRIM Watercolour Ł75
> 110 A "GREY DAY" Watercolour Ł55
>
> Gawn May
> 57 AN APPLE A DAY Watercolour Ł80
> 54 COTTAGE GARDEN Watercolour Ł70
> 55 SUNSHINE AND SHADOW (1) Watercolour Ł48
> 56 SUNSHINE AND SHADOW (2) Watercolour Ł48
>
> Gingles Thora
> 85 ANYBODY HOME? OIL Ł60
> 84 POPPIES Watercolour Ł50
> 83 ROSE OIL Ł45
>
> Bones Caroline
> 87 AT SEA Watercolour Ł50
> 88 COTTAGE IN ACHILL ISLAND Watercolour Ł55
> 86 DONEGALL COTTAGE Watercolour Ł40
> 91 CALM WATERS Watercolour Ł55
> 90 GALWAY COAST Watercolour Ł55
> 89 A SEASIDE COTTAGE Watercolour Ł55
>
Dave Peterson
data on each row.
It looked like Row 1 was a header, rows 2:xxxx would contain data.
It also looked like column A held the number, B held the artist/work, column C
held the medium, and column D held the price.
If this isn't right, adjust my formulas as required.
In E2, I put this formula:
=IF(A2<>"",E1,IF(B2="",E1,B2))
If F2, I put this formula:
=IF(COUNTA(A2:B2)=1,-1,IF(COUNTA(A2:B2)=0,99999,A2))
Drag down to one row past your data (to get a final separator between painters).
Select your range (A1 to Exxxx) and do Data|Sort (by column E, then F).
====
One more thing, put 1 in G1 and 2 in G2, select G1:g2 and drag down your data.
You should get the row number for that row in column G. If you ever have to
resort back to the original sequence, you can use this column.
(It also makes testing a little simpler. No need to save, sort, close without
saving, open, etc.)
==
If this works for you, I'd even leave the formulas there. Hide the columns or
exclude them from your print range if you want.
"Martin ©ż©¬" wrote:
>
> How do I sort this list into number order keeping the artist's name
> above and together with their paintings, medium and price.
> This is only a sample of the list which takes about 7 pages A4
> The list always starts at number 1 but the total number of paintings
> will vary from exhibition to exhibition.
> Using XL 2000
> Any help to get this sorted greatly appreciated
> Martin
> ©ż©¬
> removethis to email
> ========================================================
> NO. MEDIUM PRICE
> Fleck Margaret
> 106 NASTURTIUM Watercolour Ł90
> 105 TULIPS Watercolour Ł95
> 104 RED BAY - WATERFOOT Pastel Ł100
> 107 BLUEBELLS Watercolour Ł58
>
> Fleck William
> 109 DONEGAL Watercolour Ł75
> 108 FARM HOUSE - GLENS OF ANTRIM Watercolour Ł75
> 110 A "GREY DAY" Watercolour Ł55
>
> Gawn May
> 57 AN APPLE A DAY Watercolour Ł80
> 54 COTTAGE GARDEN Watercolour Ł70
> 55 SUNSHINE AND SHADOW (1) Watercolour Ł48
> 56 SUNSHINE AND SHADOW (2) Watercolour Ł48
>
> Gingles Thora
> 85 ANYBODY HOME? OIL Ł60
> 84 POPPIES Watercolour Ł50
> 83 ROSE OIL Ł45
>
> Bones Caroline
> 87 AT SEA Watercolour Ł50
> 88 COTTAGE IN ACHILL ISLAND Watercolour Ł55
> 86 DONEGALL COTTAGE Watercolour Ł40
> 91 CALM WATERS Watercolour Ł55
> 90 GALWAY COAST Watercolour Ł55
> 89 A SEASIDE COTTAGE Watercolour Ł55
--
Dave Peterson
ec3...@msn.com
Martin 忽帕
Did what u suggested and the list gets sorted fine
The next prob is that I originally had a blank row\space between each
artist with their paintings and the next artist with his\her
paintings, which is removed during the sort, so I have to re-inset the
space manually between each.
Do u know how to get round this and keep the space\s while sorting?
Martin
忽帕
removethis to email
Robert Rosenberg
painters without using a macro. I wouldn't insert a complete blank row
anyway. that causes those blank rows to appear together. Instead, you can
achieve the same appearance by increasing the height of the row for the
first entry of each painter. Below is an example of a macro that sorts the
list by painter, then by number (both ascending), then heightens the
appropriate rows as described above. Please note that the sort assumes the
painters are in the 1st column and the numbers are in the 5th column.
To use the macro, delete all of your blank rows, then click anywhere in your
list
Sub SortPainters()
Dim rng As Range, rngSort As Range
On Error Resume Next
'grab the current region in the list
Set rngSort = Selection.CurrentRegion
On Error GoTo Error
'If the list is not a valid range, error out and show a message
If rngSort Is Nothing Then Err.Raise Number:=9999, Description:="Current
selection is not a valid range. Click somewhere inside your list before
running this routine."
'Turn off the screen to speed up the macro
Application.ScreenUpdating = False
'Autofit the rows to make them all similar heights before sorting
rngSort.EntireRow.AutoFit
'Sort the list
'Assume the Painter name is in the 1st column - Key1:=rngSort.Cells(1,
1) and
' the Number is in the 5th column - Key2:=rngSort.Cells(1, 5)
' Adjust the ", 1" and ", 5" as necessary to identify the location of
your painter and number locations.
' For example, if the Paint Numbers are really in the 2nd column of your
list, change "rngSort.Cells(1, 5)" to "rngSort.Cells(1, 2)"
rngSort.Sort Key1:=rngSort.Cells(1, 1), Order1:=xlAscending,
Key2:=rngSort.Cells(1, 5), Order2:=xlAscending, Header:=xlYes,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
'In preparation row the row height portion, re-define the sort range to
exclude the column headings
' and only include the painter name column.
' Again, this assumes the painter names are in the first column of your
list.
Set rngSort = rngSort.Offset(1, 0).Resize(rngSort.Rows.Count - 1, 1)
'Loop through the painter names and increase the row height each time a
new name is encountered
For Each rng In rngSort
'If the cell below is different, double the row height of the cell
below
'The Trim$ function pulls out extra spaces
If Trim$(rng.Offset(1, 0).Text) <> Trim$(rng.Text) Then
rng.Offset(1, 0).RowHeight = rng.Offset(1, 0).RowHeight * 2
Next rng
Exit Sub
Error:
Application.ScreenUpdating = True
MsgBox Err.Number & vbLf & vbLf & Err.Description, vbCritical, "Sort
Painters"
End Sub
--
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
"Martin 忽帕" <mar...@martinmcd.fsnet.co.uk> wrote in message
news:jmlqqug6pfqvmlq5n...@4ax.com...
Don Guillett
--
Don Guillett
SalesAid Software
Granite Shoals, TX
don...@281.com
"Martin ©¿©¬" <mar...@martinmcd.fsnet.co.uk> wrote in message
news:r6foqugdvahhli4ts...@4ax.com...
Martin 忽帕
<bla...@email.msn.com> wrote:
>Below is an example of a macro that sorts the list by painter, then by number (both ascending), then heightens the
>appropriate rows as described above.
>Please note that the sort assumes the painters are in the 1st column and the numbers are in the 5th column.
Robert
The numbers are in column 1
The Painter\Artist is in column 2
Could u tell me the appropriate changes in your macro to sort by
column 1, then 2 please
Martin
忽帕
removethis to email
>To use the macro, delete all of your blank rows, then click anywhere in your