Posted By

pckujawa on 03/02/09


Tagged

merge excel vba lookup consolidate combine


Versions (?)

Look up all occurences and merge results (consolidate)


 / Published in: Visual Basic
 

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

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

  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

Report this snippet  

You need to login to post a comment.