Advanced Search

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