The UK Home Automation Archive

Archive Home
Group Home
Search Archive


Advanced Search

The UKHA-ARCHIVE IS CEASING OPERATIONS 31 DEC 2024


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: OT Excel Query


  • To: ukha_d@xxxxxxx
  • Subject: Re: OT Excel Query
  • From: "Graham Howe" <graham@xxxxxxx>
  • Date: Thu, 03 Oct 2002 12:57:20 -0000
  • Mailing-list: list ukha_d@xxxxxxx; contact ukha_d-owner@xxxxxxx
  • Reply-to: ukha_d@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.

Home | Main Index | Thread Index

Comments to the Webmaster are always welcomed, please use this contact form . Note that as this site is a mailing list archive, the Webmaster has no control over the contents of the messages. Comments about message content should be directed to the relevant mailing list.