Offset after first match until the first occurrence of a given string

[Bering]

Hello,

I have uploaded an image of my data and the expected result highlighted in yellow.

My goal is to match ABC (or BCE, CDE etc), then offset until the first occurrence of the string "swift" is found below and get the corresponding value in column D.

No luck so far.

Thank you.

 

[Bering]

Apologies, there was a mistake in the first image, please refer to the one attached here.

 

[Cubist]

Are you still using version 2016? You can try something like this.
Edit: Didn't notice your EFG would only have 2 rows. Inconsistent with the others. This won't work.

Book2
	A	B	C	D	E	F	G	H
1
2
3			ABC			ABC	123
4			IBAN			BCE	345
5			swift	123		CDE	222
6
7			BCE
8			IBAN
9			swift	345
10
11			CDE
12			IBAN
13			swift	222
14
Sheet3

Cell Formulas
Range	Formula
F3:F5	F3	=OFFSET($C$3,(ROWS($C$3:C3)-1)*4,0)
G3:G5	G3	=INDEX($D$3:$D$13,MATCH(F3,$C$3:$C$13,0)+2)

 

[Bering]

Are you still using version 2016? You can try something like this.

Book2
	A	B	C	D	E	F	G	H
1
2
3			ABC			ABC	123
4			IBAN			BCE	345
5			swift	123		CDE	222
6
7			BCE
8			IBAN
9			swift	345
10
11			CDE
12			IBAN
13			swift	222
14
Sheet3

Cell Formulas
Range	Formula
F3:F5	F3	=OFFSET($C$3,(ROWS($C$3:C3)-1)*4,0)
G3:G5	G3	=INDEX($D$3:$D$13,MATCH(F3,$C$3:$C$13,0)+2)

Thank you for your help. Your formula works perfectly if the swift is always in the 3rd position after the first match and there is a consistent number of blank rows. Unfortunately my source file is a mess, with thousands of rows.

 

[Cubist]

I can offer a VBA solution if you're ok with that.

 

[Bering]

I can offer a VBA solution if you're ok with that.

sure, thank you!

 

[Cubist]

Try this on a copy. It assume your data lay out is exactly like the pictures you provided.

Sub FindSwift()
    Dim a, b
    Dim i As Long, j As Long
    Dim numRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'change sheet name as needed
   
    numRow = ws.Cells(Rows.Count, "C").End(xlUp).Row '
    a = ws.Range("C3:D" & numRow).Value
    ReDim b(1 To numRow, 1 To 2)
    j = 1
 
    For i = 1 To numRow - 2
        If a(i, 1) <> "IBAN" And a(i, 1) <> "swift" And Not IsEmpty(a(i, 1)) Then
            b(j, 1) = a(i, 1)
            j = j + 1
        ElseIf a(i, 1) = "swift" Then
            b(j - 1, 2) = a(i, 2)
          
        End If
    Next i
    ws.Range("I3").Resize(j - 1, 2).Value = b
End Sub

 

[Bering]

Try this on a copy. It assume your data lay out is exactly like the pictures you provided.

Sub FindSwift()
    Dim a, b
    Dim i As Long, j As Long
    Dim numRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'change sheet name as needed
  
    numRow = ws.Cells(Rows.Count, "C").End(xlUp).Row '
    a = ws.Range("C3:D" & numRow).Value
    ReDim b(1 To numRow, 1 To 2)
    j = 1
 
    For i = 1 To numRow - 2
        If a(i, 1) <> "IBAN" And a(i, 1) <> "swift" And Not IsEmpty(a(i, 1)) Then
            b(j, 1) = a(i, 1)
            j = j + 1
        ElseIf a(i, 1) = "swift" Then
            b(j - 1, 2) = a(i, 2)
         
        End If
    Next i
    ws.Range("I3").Resize(j - 1, 2).Value = b
End Sub

Thank you so much, it's work wonderfully!!

 

[Cubist]

You're welcome. Cheers.

 

[Bering]

You're welcome. Cheers.

Hi Cubist,

Hope you don't mind me asking a little modification..
I'd like to get the same result as in column J (the swift code) but without the macro writing the other codes in column I, as those are hardcoded.

Thank you in advance.