# Posted By

pckujawa on 03/02/09

# Statistics

Viewed 420 times
Favorited by 0 user(s)

# 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`

Copy this code and paste it in your HTML
`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`