thank you steve_mac that worked a treat!!!

"Stevie_mac" wrote:

I knocked up a macro that should give the following results...

ID
Stage
Version
Date

169646
Feasibility
0
02-01-2005

169646
Conceptual
0
13-03-2005

169646
Definition + Design
1
06-03-2005

169646
Build + Implement
2
08-12-2005

123497
Feasibility
0
09-01-2005

123497
Conceptual
1
11-04-2005



Is this what you need? If so then...

Heres what to do...
* Open VB (Alt+F11)
* In the Project Explorer, right click the project & add a Class
* Name the new Class MyDataRow
* Add the following code to the class

Public ID As String
Public Stage As String
Public Ver As Long
Public EntryDate As String


* In the Project Explorer, right click the project & add a Module
* Add the following 2 functions to that module

Public Sub GetIDs()
Dim rResults As Range
Dim rTest As Range
Dim wkSheet As Worksheet
Dim OutputSheet As Worksheet
Dim dic, key, sKey As String
Set dic = CreateObject("Scripting.Dictionary")

'******* Set-up ********

'Set Results sheet name
Set OutputSheet = Sheets("Results")
'Set source data sheet name
Set wkSheet = Sheets("Sheet1")
'Set start point (should be 1st item in ID)
Set rTest = wkSheet.Range("A2")
'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'*******Read table in wkSheet********

'loop through sheets

While rTest.Text < ""
'Build 2 part 'ID.Stage' key for dic object
sKey = rTest.Text & "|" & rTest.Offset(0, 1).Text
'Test see if ID.Stage key has already been seen
If dic.Exists(sKey) Then
'YES - See if this ID.Stage is highest Ver.
If dic(sKey).Ver < rTest.Offset(0, 2).Value Then
Set dic(sKey) = BuildDataRow(rTest)
End If
Else
'NO - Store this DataRow as highest ID.Stage
Set dic(sKey) = BuildDataRow(rTest)
End If
Set rTest = rTest.Offset(1)
Wend

'*******List Results********
'Clear output sheet
OutputSheet.Cells.Clear


'Set up titles
rResults.Value = "ID"
rResults.Offset(0, 1).Value = "Stage"
rResults.Offset(0, 2).Value = "Version"
rResults.Offset(0, 3).Value = "Date"

'Set rResults to start output next row
Set rResults = rResults.Offset(1, 0)



For Each key In dic.Keys
'Put ID in cell
rResults.Value = dic(key).ID

'Put Stage in cell.offset 1 col
rResults.Offset(0, 1).Value = dic(key).Stage

'Put Ver in cell.offset 2 cols
rResults.Offset(0, 2).Value = dic(key).Ver

'Put Date in cell.offset 3 cols
rResults.Offset(0, 3).Value = dic(key).EntryDate

'reference next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


Private Function BuildDataRow(rStart As Range) As MyDataRow
Dim mdr As New MyDataRow
mdr.ID = rStart.Text
mdr.Stage = rStart.Offset(0, 1).Text
mdr.Ver = rStart.Offset(0, 2).Value
mdr.EntryDate = rStart.Offset(0, 3).Text
Set BuildDataRow = mdr
End Function


* Add a new WorkSheet & call it Results
* Now its done - Run macro GetIDs



"leolin" wrote in message ...
im trying to pick up the date with the highest version number for each stage
for each ID (there could be infinite versions). i have no idea where to
start!!

ID Stage Version # Date

169646 Feasibility 0 1/02/2005
169646 Conceptual 0 13/03/2005
169646 Definition + Design 0 1/04/2005
169646 Definition + Design 1 3/06/2005
169646 Build + Implement 0 5/07/2005
169646 Build + Implement 1 23/07/2005
169646 Build + Implement 2 12/08/2005
123497 Feasibility 0 1/09/2005
123497 Conceptual 0 25/10/2005
123497 Conceptual 1 4/11/2005

really appreciate your help here, thanks!