snapshot help

[krisso]

Book1.xls
	A	B	C	D	E	F
1	F.E	T.L	Amountof3rdPartyCheques	AmountofProlegalCheques	TotalChequesOutstanding	%Outstanding
2	RXC	RXC	0	0	0	0%
3	KXB	RXC	1	0	1	1%
4	KXF	RXC	1	1	2	2%
5	MXF	RXC	0	0	0	0%
6	SYM	RXC	1	0	1	1%
7	RXP	RXC	4	0	4	4%
8	JXT	RXC	0	0	0	0%
9	JYT	RXC	0	0	0	0%
10	EXW	RXC	0	0	0	0%
11	PXV	RXC	0	0	0	0%
12	NXG	NXG	0	0	0	0%
13	MXB	NXG	1	0	1	1%
14	ADL	NXG	7	8	15	14%
15	AXK	NXG	0	0	0	0%
16	TXP	NXG	2	0	2	2%
17	NXS	NXG	1	2	3	3%
Sheet1

what would be the easiest way to get this list to snapshot the top 10 outstanding totals to a different worksheet preferably in a macro or on opening the spreadsheet

Regards

Chris

 

[al_b_cnu]

Hi,

If you add a 'Rank' column (see below), you can simply use autofilter to select the top 10:

Book1
	A	B	C	D	E	F	G
1	F.E	T.L	Amount of 3rd Party Cheques	Amount of ProLegal Cheques	Total Cheques O/S	O/S	Rank
2	RXC	RXC	0	0	0	0%	9
3	KXB	RXC	1	0	1	1%	6
4	KXF	RXC	1	1	2	2%	4
5	MXF	RXC	0	0	0	0%	9
6	SYM	RXC	1	0	1	1%	6
7	RXP	RXC	4	0	4	4%	2
8	JXT	RXC	0	0	0	0%	9
9	JYT	RXC	0	0	0	0%	9
10	EXW	RXC	0	0	0	0%	9
11	PXV	RXC	0	0	0	0%	9
12	NXG	NXG	0	0	0	0%	9
13	MXB	NXG	1	0	1	1%	6
14	ADL	NXG	7	8	15	14%	1
15	AXK	NXG	0	0	0	0%	9
16	TXP	NXG	2	0	2	2%	4
17	NXS	NXG	1	2	3	3%	3
Sheet1

 

[krisso]

I have tried this and it doesn't work as it only gives me about 10 results for the list and i have 60 people in the list at present and its growing

regards

chris

 

[al_b_cnu]

Hi Chris,

If you select 'Top Ten', you can change the number from 10 to whatever.

Alternatively, why not just sort the list in descending order of rank?

 

[krisso]

Alan,

What i am saying is the rank formula show me less than 10 different numbers as results and it should show more, and i wanted to actually have the results shown on a different worksheet as opposed to the one i am working in.

Regards

chris

 

[al_b_cnu]

Hi,

This code expects the following:
1) format of sheet 1 as per my previous post INCLUDING the ranking in column G
2) Output to be placed in Sheet2

Sub OSTop()
Const cCols As Integer = 7
Const cNumRqd As Integer = 10
Dim iCol As Integer, iCount As Integer, iArrayPtr As Integer
Dim lRow As Long, lRowEnd As Long
Dim R As Range
Dim sFirstAdd As String
Dim vTopOS() As Variant
Dim wsFr As Worksheet, wsTo As Worksheet
Set wsFr = Sheets("Sheet1")
Set wsTo = Sheets("Sheet2")

'*************************************
'** Clear 'To' sheet & set headings **
'*************************************
wsTo.Cells.ClearContents
wsTo.Range("A1:G1").Value = wsFr.Range("A1:G1").Value

lRowEnd = wsFr.Cells(Rows.Count, "E").End(xlUp).Row

For iCount = 1 To cNumRqd
    With wsFr.Range("G2:G" & wsFr.Cells(Rows.Count, "G").End(xlUp).Row)
        Set R = .Find(iCount, LookIn:=xlValues)
        If Not R Is Nothing Then
            sFirstAdd = R.Address
            Do
                iArrayPtr = iArrayPtr + 1
                If iArrayPtr > cNumRqd Then Exit For
                lRow = R.Row
                ReDim Preserve vTopOS(1 To cCols, 1 To iArrayPtr)
                For iCol = 1 To cCols
                    vTopOS(iCol, iArrayPtr) = wsFr.Cells(lRow, iCol).Value
                Next iCol
                Set R = .FindNext(R)
            Loop While Not R Is Nothing And R.Address <> sFirstAdd
        End If
    End With
Next iCount
wsTo.Range("A2:" & Cells(UBound(vTopOS, 2) + 1, cCols).Address) = WorksheetFunction.Transpose(vTopOS)

End Sub