Excell VBA Sub to search upto 19 Columns and upto 150,000 Rows

[randomguy013]

I have a very weird Ask I have a excel sheet with a huge amount of data. 3 sheets one with 150,000 rows of data and 19 columns and 2 other sheets that are not as large but up to 50,000 rows of data and up to as many columns. I then have an entry Sheet That allows a user to enter data into any of the 19 Columns. The 3 sheets each have some type of unique structure/name to the column so I seperate Columns 15-upto 19 into sheet related entries. The first 14 have the same named columns on each sheet. The user can enter data into 1 upto 27 unique cells. 14 shared and between 3-5 unique column names.

What I am tryign to do is take the inputs from all the fields entered and compare them to each sheet and return all the values into one cell with comma between if all the input criteria matches the Data sheets. SO if a user inputs only 1 piece of data in the entry it returns all columns that matched that entry with a comma into 1 cell. If a user put in 2 entries. The returned data has to match both.

I currently have a excel function that works. It is not clean/fast however. What I am doing is Concatenating all the values the User inputs into a new cell. I am then using that as a lookup value. I then on the Data sheet have a helper column that Concatenates all the fields based off the input sheet. So it checks to see if Col 1 on Search Sheet has a value. If so it then adds Col 1 on Sheet 1 in addition to whatever other columns have values on the search Sheet.

Is there a better way to do this so I don't have to have that concat value slowing down my search. I would be ok witha sub routine that does everythign. I currently have autocaluate off as it slows down. I then have the user select which sheet they want to search or if they want to check all if they don't know. I then calculate each sheet seperately based off that input. That helps speed it up but not enough. Any help or recomendation would be appreciated.

Also I know a database would be better to do this in. I have a version going for that. However access to that will not be available 100% This excel file would. Which is why I am also looking at ways to improve this when we lose access to that.

Here is what my Search Sheet looks like
https://ibb.co/8BMZJ1B
Here is what 1 of my Data Sheets looks like with the actual colum data covered over.
https://ibb.co/ZcWcnkQ

Here would be the formula currently for Cell B13 in search Sheet "=LookupCSVResults1(B10,'Sheet 1'!U2:U150000,'Sheet 1'!A2:A150000)"

Option ExplicitFunction LookupCSVResults1(Lookupvalue As Variant, LookupRange As Range, resultsRange As Range) As String


    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust


    s = strDelimiter
    For r = 1 To LookupRange.Rows.Count
        For c = 1 To LookupRange.Columns.Count
            If LookupRange.Cells(r, c).Value = Lookupvalue Then
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next


    'Now make it look like CSV
    s = Replace(s, strDelimiter, ";")
    If Left(s, 1) = ";" Then s = Mid(s, 2)
    If Right(s, 1) = ";" Then s = Left(s, Len(s) - 1)


    LookupCSVResults1 = s 'Return the function


End Function

 

[DanteAmor]

Unfortunately the images that you put do not contain data.


So, I'm trying to understand what you want to look for, where you want to look for it and what you expect from the result.


It occurs to me, when the user captures a data, the search is made and I put the result.


You could prepare a file with examples explaining what you need.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.