Re: OT Excel Query

["Graham Howe" <graham@xxxxxxx>]

> 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

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.