Hey all, I've run into a stumper, well for me.
I am currently running the SingleCellExtract VBA (below) to present all column 2 entries for matches to column 1. My issue lies that every 2 column list has thousands of entries and so running the extract take hours and hours. Does anyone know 1. a better solution to look through the data and relay what I'm looking for and 2. that solution could be "living" so when new entries are added everything "refreshes".
This is the code I'm running:
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
Dim I As Long
Dim xRet As String
Application.ScreenUpdating = False
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
Application.ScreenUpdating = True
End Function
example of Data:
Cell A - Cell B - Cell C (were formula/code is running)
12345 - 9.15.16 - 9.15.16, 10.2.16
67890 - 9.16.16 - 9.16.16, 10.3.16
12345 - 10.2.16 - 9.15.16, 10.2.16
67890 - 10.3.16 - 9.16.16, 10.3.16
I appreciate any help with speeding this up, the first project took 12 hours to complete column C.
Thanks! Nick.
Bookmarks