+ Reply to Thread
Results 1 to 2 of 2

Please Help

  1. #1
    Registered User
    Join Date
    01-09-2005
    Posts
    3

    Question Please Help

    I've 300 odd entries in column A. I'm trying to come up with a formula to just identify the highest version and write it to a different column. Could you please help. For example I've listed below entries, I'm expecting the following entries (Iss 2.3, Jav 12.2 & YYY 1.1) to be written in say column C.

    Please help.

    Example
    Column A
    Iss 2.1
    Iss 2.2
    Iss 2.3
    Jav 12.1
    Jav 12.2
    YYY 0.0
    YYY 1.1

  2. #2
    Forum Contributor
    Join Date
    11-16-2004
    Posts
    282
    This macro assumes the following:
    1) No header in first row
    2) There are no blank cells amidst column A data
    3) The data will be sorted in acending order
    4) The first space in the text in a cell separates the "category" from its' version

    The macro reads the categories and when they change, it writes the last entry for that category to column C:


    Sub findMaxVer()
    Dim counter, rowBack, curCell, nextCell
    ActiveSheet.Range("A1").Select
    counter = 0
    ' Loop through cells to find highest version...
    Do Until IsEmpty(ActiveCell) ' Stop when first blank cell is found
    Set curCell = ActiveCell ' Get current cell data
    Set nextCell = ActiveCell.Offset(1, 0) ' Get next cell data
    If IsEmpty(nextCell) Then ' If last cell is reached, write value and stop
    ActiveSheet.Range("C" & counter + 1).Value = ActiveCell
    Exit Do
    End If
    ' Skip record if same category....
    If Mid(nextCell.Value, 1, InStr(1, nextCell.Value, " ") - 1) _
    = Mid(curCell.Value, 1, InStr(1, curCell.Value, " ") - 1) Then
    ActiveCell.Offset(1, 0).Select ' Move to next cell
    Else
    ' Otherwise, write data to column C...
    counter = counter + 1
    ActiveSheet.Range("C" & counter).Value = ActiveCell
    ActiveCell.Offset(1, 0).Select ' Move to next cell
    End If
    Loop
    End Sub

    Hope this helps,
    theDude

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1