Return to Snippet

Revision: 12091
at March 2, 2009 02:18 by pckujawa


Initial Code
Function FindNth(rTable As Range, Val1 As Variant, ResultCol As Integer) As String
     
     '''''''''''''''''''''''''''''''''''''''
     
     'Written by OzGrid Business Applications
     
     'www.ozgrid.com
     
     ' Finds ALL occurences in the first Column of a table.
     ' Merges results
     '''''''''''''''''''''''''''''''''''''''
     'Used like;
     '=FindNth($A$1:$B$37,"ALFA5010",2)
               
    Dim lLoop As Long
    Dim rFound As Range
    Dim strResults As String
     
    With rTable.Columns(1)
        Set rFound = .Cells(, 1)
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, Val1)
             
            Set rFound = .Cells.Find(Val1, rFound, xlValues, xlWhole, xlNext, xlRows, False)
            If strResults <> vbNullString Then
                strResults = strResults & rFound(1, ResultCol) ' Had "& Chr(10) &" to add CR/LF, but I removed it
            Else
                strResults = rFound(1, ResultCol)
            End If
             
        Next lLoop
    End With
    FindNth = RTrim(strResults)
End Function

Initial URL
http://www.ozgrid.com/forum/showthread.php?t=114520

Initial Description
Say you've got data combined in such a way that there is a repeated row header, but all the data is in different rows. If you were to consolidate all the rows down to one, only taking valid values (discarding blanks), you'd have one header row with all the data. This function does that for you.

Ex:  
`StudentID,TestScore1, TestScore2,TestScore3  
1,100,,  
1,,98,  
1,,,97`

Becomes:  
`StudentID,TestScore1, TestScore2,TestScore3  
1,100,98,97`

Initial Title
Look up all occurences and merge results (consolidate)

Initial Tags
excel

Initial Language
Visual Basic