Looping and sorting through blocks of rows

[zeekmcphee]

Hello
I am looking for a VBA solution
I have a sheet with several thousand rows.The rows are split up into blocks of rows and seperated by an empty row.Each block of rows will vary with entries so these are random

The sheet has 7 fields author,book_ title,publisher,year_published,genre,number,copies_sold.
The number field is a number against each book title within the block of rows

The last field..copies_sold is a numeric field and what I would like to do is sort each authors copies sold field from least sold to most sold.
If the sheet was one long continuous record then sorting would be easy,but it is a series of blocks of records for each author.
Is there a way that I could some how loop through each individual authors block and sort the copies sold field from smallest to largest;
Below is an example of just three authors,there are three blocks of rows of 4,6and7

[TABLE="width: 100"]
<tbody>[TR]
[TD]author_name.. [/TD]
[TD]book_title..[/TD]
[TD]publisher..[/TD]
[TD]year_published[/TD]
[TD]number[/TD]
[TD]genre[/TD]
[TD]copies_sold[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]swan[/TD]
[TD]dolphin[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]mystery[/TD]
[TD]26000[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]the rock[/TD]
[TD]dolphin[/TD]
[TD]2013[/TD]
[TD]2[/TD]
[TD]mystery[/TD]
[TD]18500[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]daytime[/TD]
[TD]dolphin[/TD]
[TD]2012[/TD]
[TD]3[/TD]
[TD]thriller[/TD]
[TD]670500[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]lost[/TD]
[TD]gracey[/TD]
[TD]2006[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]78000[/TD]
[/TR]
[TR]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty [/TD]
[TD]empty [/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]sunshine[/TD]
[TD]gracey[/TD]
[TD]2008[/TD]
[TD]1[/TD]
[TD]crime[/TD]
[TD]136000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]rocket[/TD]
[TD]gracey[/TD]
[TD]2006[/TD]
[TD]2[/TD]
[TD]crime[/TD]
[TD]236000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]last one in[/TD]
[TD]gracey[/TD]
[TD]2007[/TD]
[TD]3[/TD]
[TD]mystery[/TD]
[TD]900000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]waterfall[/TD]
[TD]bell[/TD]
[TD]2010[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]155000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]the ring[/TD]
[TD]bell[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]thriller[/TD]
[TD]257893[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]april[/TD]
[TD]bell[/TD]
[TD]2017[/TD]
[TD]6[/TD]
[TD]thriller[/TD]
[TD]823456[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]mrs jones[/TD]
[TD]bell [/TD]
[TD]2017[/TD]
[TD]1[/TD]
[TD]bio[/TD]
[TD]289000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]the parade[/TD]
[TD]gracey[/TD]
[TD]2017[/TD]
[TD]2[/TD]
[TD]bio[/TD]
[TD]456000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]carnival[/TD]
[TD]gracey[/TD]
[TD]2014[/TD]
[TD]3[/TD]
[TD]thriller[/TD]
[TD]234000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]valley[/TD]
[TD]dolphin[/TD]
[TD]2012[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]980000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]my tree[/TD]
[TD]dolphin[/TD]
[TD]2010[/TD]
[TD]5[/TD]
[TD]romance[/TD]
[TD]345000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]talk[/TD]
[TD]dolphin[/TD]
[TD]2009[/TD]
[TD]6[/TD]
[TD]romance[/TD]
[TD]650000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]smile[/TD]
[TD]bell[/TD]
[TD]2003[/TD]
[TD]7[/TD]
[TD]thriller[/TD]
[TD]345000[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense
ZM

 

[Fluff]

How about

Sub rankandSort()
   
   Dim Rng As Range
   Dim Ar As Areas
   
   Set Ar = Range("A2", Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
   Columns(5).Insert
   Range("E1").Value = "Position"
   
   For Each Rng In Ar
      Rng.Sort key1:=Range("F:F"), order1:=1
      With Intersect(Rng, Range("E:E"))
         .FormulaArray = "=rank(" & .Offset(, 1).Address & "," & .Offset(, 1).Address & ",1)"
         .Value = .Value
      End With
   Next Rng
End Sub

 

[hiker95]

Re: Looping and sorting through blocks of rows
Hello Hiker95,
Many many thanks for that code it works perfectly.

zeekmcphee,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[zeekmcphee]

Hi Fluff
Thanyou for that reply
When I run the code it inserts Position into column E and pushes score into column F, the sort in column F works great but then every cell in the newly created Position column gets filled with what seems to be a formula:

=RANK(R2C6,R5C6,R2C6:R5C6,1)

which iterates as it goes through the cells
Any suggestions are welcome
Kind Regards
ZM

 

[Fluff]

It puts a formula into col E but then this line

 .Value = .Value

Should then convert that formula into values. Is that not happening?

 

[zeekmcphee]

Hi Fluff,
Sorry My fault I just realised that my "score"field was was text,once I converted that into a number the program works perfectly.
Many many thanks for that great bit of coding

Kind Regards
ZM

 

[Fluff]

Glad we could help & thanks for the feedback

 

[zeekmcphee]

Hi Dryver
Thats probably my fault I have been messing about with half a dozen sheets and getting a bit mixed up.,But much thanks
for your help
Kind Regards
ZM

 

[zeekmcphee]

Following on from my original question.I have another spreadsheet that works in a similar way to this one i.e the records are split up into groups.,and it documents students maths marks over a given time.Here is an example.What I would like to do is add another column(perhaps bewteen week and score columns)
that gives the position of the student in that record

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]name[/TD]
[TD]year[/TD]
[TD]level[/TD]
[TD]week[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]17[/TD]
[TD]61[/TD]
[/TR]
</tbody>[/TABLE]



to this
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]name[/TD]
[TD]year[/TD]
[TD]level[/TD]
[TD]week[/TD]
[TD]position[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]3[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]4[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]4[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]89[/TD]
[/TR]
</tbody>[/TABLE]



So the position column reflects the new position of the score after the sort.
If two scores are the same then that will be a joint position,as with John Ellis joint 4th with tow sets of 54 and phil simm who has a joint 1st and 4th.
Hope this nakes sense.Any help much appreciated
regardsZM

 

[Fluff]

In what way is this different to post#4?

 

[zeekmcphee]

Hi Fluff,

Both questions are more or less identical,nobody seemed to pick up on it earlier so I reproduced it here .
kind regards
zm