Data cleaning, HELP!

[a303]

Please download the attached excel workbook.

It has data in the following format

Please Login or Register  to view this content.

I want to arrange this section of data in the following format:
1. Each row is a record, so I would like to keep the rows as a whole
2. It should be arranged in decreasing order of column B (and increasing order of column A). I have already taken care of this operation.
3. I want it to fill the missing numbers in column B (497 and 494) in this example. That new row (with the new numbers in column B) will not contain any other data, i.e., columns A, C and D should be blank.

My problem is that there are many such sections of data in the worksheet arranged side by side with one column to separate them (as you can see in the WB). In each section's column B, different numbers will be missing.

For all sections, I want the second columns to have numbers ordered in decreasing fashion between a certain range, say 585 (at the top) to 475 (at the bottom) (see worksheet please). Therefore, the length of each table must be the same.

I have come to this stage after a lot of data manipulation and this is the last bit. The looping structure seems to confound me the most, can someone help me??

[duane]

Try this out

Sub Macro1()
For i = 1 To 150 Step 5
If i > 1 Then ActiveWorkbook.Names("B").Delete
Range(Cells(3, i + 1), Cells(113, i + 1)).Name = "B"
If Cells(3, i) = 0 Then GoTo done
Range(Cells(3, i), Cells(113, i + 3)).Select
Selection.Sort key1:=Range("B"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
If Cells(3, i + 1) = 585 Then GoTo tester
Range(Cells(3, i), Cells(113, i + 3)).Select
Selection.Cut
Cells(4, i).Select
ActiveSheet.Paste
Cells(3, i + 1) = 585
tester:
For j = 4 To 113
If Cells(j, i + 1) = Cells(j - 1, i + 1) - 1 Then GoTo nextj
inserter:
Range(Cells(j, i), Cells(113, i + 3)).Select
Selection.Cut
Cells(j + 1, i).Select
ActiveSheet.Paste
Cells(j, i + 1) = Cells(j - 1, i + 1) - 1
nextj:
Next j
nexti:
Next i
done:
End Sub

[antoka05]

See the attached file.

Here I added a macro to do what you need.
It runs for all section present in the worksheet.

I hope it can help you.

Regards,
Antonio

[a303]

Both your macros run fine Thanks a lot, you guys saved me hours of work.