Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > trying to find max date
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
leolin
 
Posts: n/a
Default trying to find max date
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!


Reply With QuoteReply With Quote
  #2   Report Post  
Stevie_mac
 
Posts: n/a
Default
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!




Reply With QuoteReply With Quote
  #3   Report Post  
LanceB
 
Posts: n/a
Default
=SUMPRODUCT((MAX(($B$1:$B$10="conceptual")*($C$1:$ C$10))=$C$1:$C$10)*($B$1:$B$10="conceptual")*$D$1: $D$10)

"leolin" wrote:

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!


Reply With QuoteReply With Quote
  #4   Report Post  
leolin
 
Posts: n/a
Default
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!





Reply With QuoteReply With Quote
  #5   Report Post  
leolin
 
Posts: n/a
Default
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!!

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





Reply With QuoteReply With Quote
  #6   Report Post  
Stevie_mac
 
Posts: n/a
Default
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


"leolin" wrote in message ...
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!!

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







Reply With QuoteReply With Quote
  #7   Report Post  
leolin
 
Posts: n/a
Default
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!

"Stevie_mac" wrote:

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


"leolin" wrote in message ...
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!!

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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to find oldest date in ever changing list. Alan Anderson via OfficeKB.com Excel Worksheet Functions 5 February 20th 05 04:09 AM
Find Max after a date Bruce Excel Worksheet Functions 3 January 18th 05 02:57 AM
find date in Col A corresponding to min value in Col B gregl Excel Worksheet Functions 15 November 19th 04 04:01 AM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 0 October 28th 04 08:03 PM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 1 October 28th 04 09:52 AM


All times are GMT +1. The time now is 11:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement