trying to find max date

G

Guest

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!
 
S

Stevie_mac

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
 
G

Guest

=SUMPRODUCT((MAX(($B$1:$B$10="conceptual")*($C$1:$C$10))=$C$1:$C$10)*($B$1:$B$10="conceptual")*$D$1:$D$10)
 
G

Guest

thank you steve_mac that worked a treat!!!

Stevie_mac said:
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
Click to expand...
 
G

Guest

to stevie_mac,

so its always not as easy as it seems, i thought i could use your code as a
starting point, but now im getting errors!

the source data is actually in another separate spreadsheet, and the columns
are not in succession: column A = ID, column I = stage, column J = version #,
column K = date (start). and there is another (end) date column i would also
like to pick up from column L.

i get the error 'subscript out of range', would really appreciate your
continued help on this, thanks!!
 
S

Stevie_mac

1st, ensure the Sheet exists with the correct name (customise the GetIDs SUB in the Set-up part)
2nd, I've made it more parameterised so you can 'Set-up' how you need...

As before...
* 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 (UPDATED) functions to that module

Private Function BuildDataRow(ID As String, _
Stage As String, _
Ver As Single, _
EntryDate As String) As MyDataRow
Dim mdr As New MyDataRow
mdr.ID = ID
mdr.Stage = Stage
mdr.Ver = Ver
mdr.EntryDate = EntryDate
Set BuildDataRow = mdr
End Function



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

'******* Set-up ### Customise here ### ********

'Set Results sheet name
Set OutputSheet = Workbooks("Output.xls").Sheets("Results")
'Set source data sheet name
Set wkSheet = ThisWorkbook.Sheets("Sheet1")

'Set start points
Set rID = wkSheet.Range("A2")
Set rStage = wkSheet.Range("B2")
Set rVer = wkSheet.Range("C2")
Set rDate = wkSheet.Range("D2")

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

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

'loop through items

While rID.Text <> ""
'Build 2 part 'ID.Stage' key for dic object
sKey = rID.Text & "|" & rStage.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 < rVer.Value Then
Set dic(sKey) = BuildDataRow(rID.Text, _
rStage.Text, _
rVer.Value, _
rDate.Text)
End If
Else
'NO - Store this DataRow as highest ID.Stage
Set dic(sKey) = BuildDataRow(rID.Text, _
rStage.Text, _
rVer.Value, _
rDate.Text)
End If
Set rID = rID.Offset(1)
Set rStage = rStage.Offset(1)
Set rVer = rVer.Offset(1)
Set rDate = rDate.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

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

'Show results
OutputSheet.Activate
End Sub


* Now its done - Run macro GetIDs


Let me know how you get on.

Regards - Steve
 
G

Guest

you're pure genius! thank you!

just one thing, i noticed that my date format changed from dd/mm/yyyy to
mm/dd/yyyy. ive tried formatting the cells but this doesnt work.

thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help with formula (multiple ifs to determine due date) 3
Even more Complex COUNTIF functionality 6
Is this possible 2
Login Logout Date Problem 2
IRR & XIRR - Different Results 10
Summing weekending dates 1
possible countif formula? 3
count if with two references 1

Top