Originally Posted by
johnmacpro
i want find SAP KEY from ref's sheet start with min(Reference_2) range of (G2:H3), If concante column G:G match in range get result H:H, once done delete fist 3 row ,bring the next 2 row up. this action get change depends upon ref-sheet
if its Ref_4 delete first 5 row bring up next 3
if its Ref_3 delete first 4 row bring up next 3
there is one challenge, if combination order in 1,2 in report data sheet 2,1 need to find exact combination
Fixed the code for the second red part.
This extracts result in order of each sheet according to the first matched row.
Sub test()
Dim ws As Worksheet, i As Long, ii As Long, n, s, x, flg As Boolean
Application.ScreenUpdating = False
With Sheets("Reporting_Data_Input").[a1].CurrentRegion
.Columns("i").Offset(1).ClearContents
For i = 2 To .Rows.Count
n = .Cells(i, "f"): s = "reference_" & n: flg = False
If Evaluate("isref('" & s & "'!a1)") Then
Set ws = Sheets(s): ReDim x(n - 1)
For ii = 0 To n - 1
s = Join(Array(.Cells(i + ii, 2), .Cells(i + ii, 3), .Cells(i + ii, 4)), "_")
x(ii) = ws.Evaluate("match(""" & s & """&""*"",g:g,0)")
If IsError(x(ii)) Then flg = True: Exit For
Next
If Not flg Then
x = Application.Min(x)
.Cells(i, "i").Resize(n).Value = ws.Cells(x, "h").Resize(n).Value
ws.Rows(x).Resize(n + 1).Delete
Else
.Cells(i, "i").Resize(n) = "No match"
End If
End If
i = i + n - 1
Next
End With
Application.ScreenUpdating = True
MsgBox "done"
End Sub
Bookmarks