RE: Re: OT Excel Query

["Daniel James" <groups@xxxxxxx>]

Many thanks for this Graham, I'll give it a whirl tomorrow.

 

Cheers

 

Daniel

-----Original Message-----
From: Graham Howe [mailto:graham@xxxxxxx]
Sent: 03 October 2002 13:57
To: ukha_d@xxxxxxxSubject: [ukha_d] Re: OT Excel Query

> Is there an easy way of doing this, I have a few thousand of them
to
> do, so manually isn't an option!
>
Here is a macro for Excel that does what you need. It assumes that
data is initially in a two column list with a header row and no blank
lines. The top cell in this list (in other words the heading 'name')
should be range named "names".

The resultant list will be placed in an area beneath a range
named "results".

The macro should be pretty quick even with a few thousand rows.

Sub report()
    dataRows = Range("names").End(xlDown).Row - 1
    Range("names", Range("names").End(xlDown).Offset(0, 1)).Select
    Selection.Sort _
        Key1:=Range("names").Offset(1, 0), Order1:=xlAscending, _
        Key2:=Range("names").Offset(1, 1), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom
    Range("names").Select
    nCnt = 0
    For rowOffset = 1 To dataRows
        thisName = ActiveCell.Offset(rowOffset, 0)
        thisCourse = ActiveCell.Offset(rowOffset, 1)
        prevName = ActiveCell.Offset(rowOffset - 1, 0)
        If thisName <> prevName Then
            nCnt = nCnt + 1
            cCnt = 1
            Range("results").Offset(nCnt, 0).Value = thisName
            Range("results").Offset(nCnt, cCnt).Value = thisCourse
        Else
            cCnt = cCnt + 1
            Range("results").Offset(nCnt, cCnt).Value = thisCourse
        End If
    Next
End Sub


Regards

Graham



http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx
Subscribe:  ukha_d-subscribe@xxxxxxx
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx
List owner:  ukha_d-owner@xxxxxxx
List of UKHA Groups here - http://groups.yahoo.com/group/UKHA_Grouplists/

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

Yahoo! Groups Sponsor

ADVERTISEMENT

http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx 
Subscribe:  ukha_d-subscribe@xxxxxxx 
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx 
List owner:  ukha_d-owner@xxxxxxx
List of UKHA Groups here - http://groups.yahoo.com/group/UKHA_Grouplists/

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.