VBA for extracting different results based on multiple criterias

[siduri]

Dear forum,

Since I am very new to VBA and programming in general i need some help with some VBA code that returns some results based on multiple criterias.
In the worksheet I have attached I want to do the following:
Considering the values in cells A, B and C (story, beam, load names) as the criterias (which I introduce in cells L, M and N) I want to fill the columns O to U with the coressponding values from cells D to J.
The problem is how do I do this so that the code fills multiple rows since results are multiple for the same criteria.
Since I have alot of data that keeps changing and which can be anywhere from 100 rows to 20000 rows I would also like to refill/refresh the result cells whenever dtat in the sheet changes.

I tried an array formula, which I just drag diown, using countifs but it just takes ages for the cells to update when i chage the value of one criteria.

Thank you in advance

[mike7952]

give this a try

Sub abc()
 Dim a, y, i As Long, ii As Long
 
 a = Range("a1").CurrentRegion
 ReDim y(1 To UBound(a), 1 To 7)
 For i = 2 To UBound(a)
    For ii = 1 To 7
        If a(i, 1) = Cells(2, "L") And a(i, 2) = Cells(2, "M") _
            And a(i, 3) = Cells(2, "N") Then
            y(i - 1, ii) = a(i, ii + 3)
        Else
            y(i - 1, ii) = Empty
        End If
    Next
 Next
 Range("O2").Resize(UBound(y), UBound(y, 2)) = y
End Sub

[siduri]

Works great. Thanly you very much Mike

[JBeaucaire]

Yeah, arrays are a bother when you try to apply them to an entire column like that. With datasets this large it will be less cumbersome on performance to use a helper column. See the attached, performance is returned, you can replace data in columns A:J, the key column K has formulas going down 20k rows and is still very quick and no VBA.

[mike7952]

@JBeaucaire

Im not sure but I thinking the OP wanted to show results in the same row as the match was found. I dont know?

[JBeaucaire]

His original sheet (and problematic formulas) were in the LOCATION column on the right. Those are what I removed and replaced to return performance to the sheet.

[mike7952]

Yea your probably right? ;-). Im just not good with formulas, not that I havent tried.

[siduri]

Thanks JBeaucaire, it's nice to have 2 different examples.
Yes I wanted to show the data at the top of the spread sheet not where the match was found. I don't think it will bother me so much if I can copy the results returned to another spread sheet at the top in case I decide to go with the VBA version.
I was planing to use one spread sheet to dump the data and then to put the criteria and obtain the results into another sheet, where I will use them for some calculations.

Cheers

[mike7952]

Ok this will give the results at the top

Sub abc()
 Dim a, y, i As Long, ii As Long, n As Long
 
 a = Range("a1").CurrentRegion
 ReDim y(1 To UBound(a), 1 To 7)
 For i = 1 To UBound(a)
    If a(i, 1) = Cells(2, "L") And a(i, 2) = Cells(2, "M") _
       And a(i, 3) = Cells(2, "N") Or i = 1 Then
       n = n + 1
        For ii = 4 To UBound(a, 2)
            y(n, ii - 3) = a(i, ii)
        Next
    End If
 Next
  With Range("O1").Resize(n, UBound(y, 2))
    .Clear
    .Value = y
 End With
End Sub

and if you want to dumb these results to a different tab change this line

 Range("O2").Resize(n, UBound(y, 2)) = y

to and also can change O2 to A1 if you want

 Worksheets("SheetNameWhatever").Range("A1").Resize(n, UBound(y, 2)) = y

[siduri]

Thank you