Posted By

pckujawa on 03/02/09

Tagged

excel

Versions (?)

Last Edited at 03/02/09 02:18am

Statistics

Viewed 531 times

Favorited by 0 user(s)

Related snippets

Look up all occurences and merge results (consolidate)

/ Published in: Visual Basic

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`

Highlighted TextExpand

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

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.