Advanced Search

Look up all occurences and merge results (consolidate)

/ Published in: Visual Basic
Save to your folder(s)
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`

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Function FindNth(rTable As Range, Val1 As Variant, ResultCol As Integer) As String
  2.  
  3.      '''''''''''''''''''''''''''''''''''''''
  4.  
  5.      'Written by OzGrid Business Applications
  6.  
  7.      'www.ozgrid.com
  8.  
  9.      ' Finds ALL occurences in the first Column of a table.
  10.      ' Merges results
  11.      '''''''''''''''''''''''''''''''''''''''
  12.      'Used like;
  13.      '=FindNth($A$1:$B$37,"ALFA5010",2)
  14.  
  15.     Dim lLoop As Long
  16.     Dim rFound As Range
  17.     Dim strResults As String
  18.  
  19.     With rTable.Columns(1)
  20.         Set rFound = .Cells(, 1)
  21.         For lLoop = 1 To WorksheetFunction.CountIf(.Cells, Val1)
  22.  
  23.             Set rFound = .Cells.Find(Val1, rFound, xlValues, xlWhole, xlNext, xlRows, False)
  24.             If strResults <> vbNullString Then
  25.                 strResults = strResults & rFound(1, ResultCol) ' Had "& Chr(10) &" to add CR/LF, but I removed it
  26.             Else
  27.                 strResults = rFound(1, ResultCol)
  28.             End If
  29.  
  30.         Next lLoop
  31.     End With
  32.     FindNth = RTrim(strResults)
  33. End Function

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.