Thread: Extraction of values following a pattern

Hi,

I have the following problem that I don't succeed to solve.
I actually couldn't find a correct way to describe it in the title.

It is easier for me to summarize it with the following example:

----- are just here act as a separator between columns A and B
All the letters are in column A
All the numbers are in column B

I'm looking for a way to find where is AAA (it won't be the first line), then to find the next line where is located the exact value "owned", then to take the value in the column B for this same line.
Then to repeat the same process for BBB CCC ...

I don't have the same numbers of lines between "XXX" and "owned"

AAA
da
wf
t3
owned ------ 26
BBB
fq
sdf
wt
vreg
erqtg
owned ------- 12
CCC
sf
sdf
owned -------- 23
…

The result that I expect is like this:

AAA 26
BBB 12
CCC 23
… …

in two columns.

I know (more or less) how to use VLOOKUP.
I'm mainly stuck for everything else regarding this problem.

Any help to solve it would be greatly appreciated, or at least to be directed to the exact formulas that I will have to use.

Thank you.

Strings in column A.
Values in column B.

AAA in D1, this formula in E1:

=VLOOKUP("owned", OFFSET(INDEX(A:A, MATCH(D1,A:A, 0)),,,COUNTA(A:A),2), 2, 0)

This part of the formula is finding the "row" the value of D1 is on by searching column A:

MATCH(D1,A:A, 0)

Then adding that to the INDEX results in a cell value:

INDEX(A:A, MATCH(D1,A:A, 0))

We then insert that as the first parameter of an OFFSET() function, and expand the result to start from that cell downward, and expand to two columns.

OFFSET(INDEX(A:A, MATCH(D1,A:A, 0)),,,COUNTA(A:A),2)

That creates a two-column range in A:B that starts at the value from D1, the goes down. All of that is inserted into a standard VLOOKUP to the get "owned" row and the adjacent value:

=VLOOKUP("owned", OFFSET(INDEX(A:A, MATCH(D1,A:A, 0)),,,COUNTA(A:A),2), 2, 0)

As a formula, that's only a small tweak. Moving over to column K to give room for those extra columns of data...

Then with AAA in K1, the first formula in L1 would be:

=VLOOKUP("owned", OFFSET(INDEX($A:$A, MATCH($K1,$B:$B, 0)),,,COUNTA($A:$A),10), COLUMN(B1), 0)

I added a new function COLUMN() to increment the returned column value as you copy the formula to the right.

This project of yours is complex enough I'd suggest using VBA might be better.

My apologies... I didn't notice the "tag" requirement, too. This definitely a magnitude of complexity harder for a "formula", and with this formula below you may start to notice "lag" in your sheet as they calculate. I only set the range for 1000 cells, hopefully that's enough.

=VLOOKUP("owned", OFFSET(INDEX($A$1:$A$1000, MATCH("tag" & $K1, INDEX($A$1:$A$1000&$B$1:$B$1000, 0), 0)),,,COUNTA($A:$A),10), COLUMN(B1), 0)

I wouldn't process 400k of data this way. When there's this much data, I'd add a "key" column to create an easy-match tag system.

Here's a sheet showing a much simpler standard VLOOKUP using an added new column A to make the lookups easy. Just add that column to your real dataset, copy formula2 all the way down.

I'm intrigued. Why would you be unable to insert a column A?

VBA can do this quite easily. That much data would take a bit of time, but by no means hard to write.

Can you select all the columns ABW:XFD and do an Edit > Clear > All ?

Here's a little macro that can extract the data to a separate sheet. The macro is in the Module1 of the VBEditor.

Option Explicit

Sub CollectTagInfo()
Dim tagFIND As Range, tagNEXTFIND As Range, tagOWNED As Range
Dim wsData As Worksheet, wsOUT As Worksheet, NR As Long

If MsgBox("Process this active sheet's data?", vbYesNo, "Confirm") _
                            = vbNo Then Exit Sub
Set wsData = ActiveSheet

With wsData
    .Rows(1).Insert xlShiftDown
    On Error Resume Next
    Set tagFIND = .Range("A:A").Find("tag", LookIn:=xlValues, LookAt:=xlWhole)
    
    If tagFIND Is Nothing Then
        MsgBox "'TAG' flag not found in column A, please recheck the data"
        .Rows(1).Delete xlShiftUp
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Set wsOUT = Sheets.Add(After:=Sheets(Sheets.Count))
    wsOUT.Range("A1:B1").Value = [{"Tag","Owned"}]
    NR = 2
    Set tagNEXTFIND = .Range("A:A").Find("tag", tagFIND, LookIn:=xlValues, LookAt:=xlWhole)
    
    Do
        wsOUT.Range("A" & NR).Value = tagFIND.Offset(, 1).Value
        Set tagOWNED = .Range(tagFIND, tagNEXTFIND).Find("owned", LookIn:=xlValues, LookAt:=xlWhole)
        If tagOWNED Is Nothing Then
            wsOUT.Range("B" & NR) = "not found"
        Else
            Range(tagOWNED, tagOWNED.End(xlToRight)).Offset(, 1).Copy wsOUT.Range("B" & NR)
            Set tagOWNED = Nothing
        End If
        
        NR = NR + 1
        Set tagFIND = tagNEXTFIND
        Set tagNEXTFIND = .Range("A:A").Find("tag", tagFIND, LookIn:=xlValues, LookAt:=xlWhole)
    Loop Until tagNEXTFIND.Row < tagFIND.Row
    
    Set tagOWNED = .Range(tagFIND, tagFIND.End(xlDown).End(xlDown)).Find("owned", LookIn:=xlValues, LookAt:=xlWhole)
    wsOUT.Range("A" & NR).Value = tagFIND.Offset(, 1).Value
    If tagOWNED Is Nothing Then
        wsOUT.Range("B" & NR) = "not found"
    Else
        Range(tagOWNED, tagOWNED.End(xlToRight)).Offset(, 1).Copy wsOUT.Range("B" & NR)
    End If
    .Rows(1).Delete xlShiftUp
    Application.ScreenUpdating = True
End With

End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select CollectTagInfo from the macro list. It will run on the "activesheet", so you can put this macro into another workbook to run from, if you wish.