Sorting mltple sections of data based on 1 cell in ea sect.

[awwphooey]

Hello-

Like a bad penny…

I am now trying to sort multiple sections of data into numerical order, based on one cell within each section. For example, I have this:

business objects spaced out test.xls
	A	B	C	D	E	F	G	H
419	CRF63637	FN944	(Review)	rg	6/29/0610:35AM	7/26/064:57PM	27:6:22
420	CRF63637	FN944	(Approve)	rg	8/1/063:12PM	9/15/064:41PM	14:1:28
421	CRF63637	FN944	(Approve)	lmilano	8/1/063:12PM	8/2/061:23PM	0:22:11
422	CRF63637	FN944	(Approve)	angela	8/1/063:12PM	8/2/0611:22AM	0:20:09
423	CRF63637	FN944	(Approve)	lhg	8/1/063:12PM	8/1/064:23PM	0:1:10
424							74days3:23	74.14
425	CRF63625	1770.039	(Approve)	bsanford	7/27/0612:00PM	8/23/065:16PM	27:5:15
426	CRF63625	1770.039	(Review)	cseidt	6/29/0610:33AM	7/20/064:32PM	21:5:59
427	CRF63625	1770.039	(Approve)	hoey	7/27/0612:00PM	9/12/063:32PM	16:3:31
428							95days14:47	95.62
Report 1

Right now, I have it configured to place the formatted totals in column G in the empty row just below each block. I also have it putting the raw totals in column K in case I need to do any further math with the numbers, as the formatted totals are text that can't be manipulated mathematically. I'd like to sort as follows:

business objects spaced out test.xls
	A	B	C	D	E	F	G	H
419	CRF63625	1770.039	(Approve)	bsanford	7/27/0612:00PM	8/23/065:16PM	27:5:15
420	CRF63625	1770.039	(Review)	cseidt	6/29/0610:33AM	7/20/064:32PM	21:5:59
421	CRF63625	1770.039	(Approve)	hoey	7/27/0612:00PM	9/12/063:32PM	16:3:31
422							95days14:47	95.62
423	CRF63637	FN944	(Review)	rg	6/29/0610:35AM	7/26/064:57PM	27:6:22
424	CRF63637	FN944	(Approve)	rg	8/1/063:12PM	9/15/064:41PM	14:1:28
425	CRF63637	FN944	(Approve)	lmilano	8/1/063:12PM	8/2/061:23PM	0:22:11
426	CRF63637	FN944	(Approve)	angela	8/1/063:12PM	8/2/0611:22AM	0:20:09
427	CRF63637	FN944	(Approve)	lhg	8/1/063:12PM	8/1/064:23PM	0:1:10
428							74days3:23	74.14
Report 1

There are multiple sections, just showing 2 for brevity.
Thanks in advance for any help available!
Phoo

 

[Boller]

Are your totals in columns G and H, or in columns G and K ?

Other than the two totals, is there anything else in the "total" rows in other columns?

 

[awwphooey]

Hi Boller-

My totals are in columns G and H, there are no other colums.

If it helps, this is the script one of the tech guys here at work gave me, which allowed me to total up the days, hours and minutes spent on each CRF (again, the CRF number is just a document number. I'm tryng to ascertain the time spent on processing each document):

Sub CalculateSubtotals()

Dim oSheet As Worksheet
Dim iRowIndex As Long
Dim sCurrentRowLabel As String
Dim sNewRowLabel As String
Dim runningTotal As Double
Dim iConsecutiveEmptyRows As Integer
Dim numDays As Integer
Dim fractionalDays As Double
Dim numHours As Integer
Dim countTotals As Long

Set oSheet = ActiveWorkbook.ActiveSheet
iRowIndex = 3
iConsecutiveEmptyRows = 0
sCurrentRowLabel = oSheet.Rows(iRowIndex).Cells(1, 1)
runningTotal = (oSheet.Rows(iRowIndex).Cells(1, 6) - oSheet.Rows(iRowIndex).Cells(1, 5))
countTotals = 0

Do While (iConsecutiveEmptyRows < 5)
iRowIndex = iRowIndex + 1
sNewRowLabel = oSheet.Rows(iRowIndex).Cells(1, 1)
If (sNewRowLabel = sCurrentRowLabel) And (sNewRowLabel <> "") Then
runningTotal = runningTotal + (oSheet.Rows(iRowIndex).Cells(1, 6) - oSheet.Rows(iRowIndex).Cells(1, 5))
Else
If (sNewRowLabel = "") And (sCurrentRowLabel <> "") Then
numDays = Int(runningTotal) - (runningTotal Mod 1)
fractionalDays = runningTotal - numDays
numMinutes = fractionalDays * 1440
numHours = (numMinutes \ 60)
numMinutes = (numMinutes - (numHours * 60)) \ 1
oSheet.Rows(iRowIndex).Cells(1, 11) = runningTotal
oSheet.Rows(iRowIndex).Cells(1, 7) = CStr(numDays) + " days " + CStr(numHours) + ":" + CStr(numMinutes)
runningTotal = 0#
countTotals = countTotals + 1
ElseIf (sNewRowLabel = "") And (sCurrentRowLabel = "") Then
' no op
Else
runningTotal = (oSheet.Rows(iRowIndex).Cells(1, 6) - oSheet.Rows(iRowIndex).Cells(1, 5))
End If

sCurrentRowLabel = sNewRowLabel
If (sNewRowLabel = "") Then
iConsecutiveEmptyRows = iConsecutiveEmptyRows + 1
Else
iConsecutiveEmptyRows = 0
End If
End If
Loop

MsgBox "Done!" + vbCrLf + vbCrLf + "Processed " + CStr(countTotals) + " totals."
End Sub

Now, I'm trying to sort each section (each CRF set), by the greatest amount of time down to the least.

Phoo