Advanced Search VBA, search text within text and pull in values

Z

Zenru

New Member
Joined
Oct 19, 2017
Messages
29
Hello everybody,


I have two sheets with information in Excel, one (SheetB) where there are unique values like the one below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]WUX[/TD]
[TD]45[/TD]
[TD]ASNDWUX45[/TD]
[TD]12000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]RL[/TD]
[TD]PUGBSCARL[/TD]
[TD]5000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]WUX[/TD]
[TD]48[/TD]
[TD]ASNDWUX48[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQN5[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R8[/TD]
[TD]PUGBSCAR8[/TD]
[TD]5155[/TD]
[TD]1548[/TD]
[/TR]
</tbody>[/TABLE]




And I have another sheet (SheetA), with the same values as above, but:

  • They repeat
  • Some might have in part3 concatenated information like: 45, 4F, 65D, S


So, I need a macro that, when a value changed in a specific range in SheetB (range might grow in rows, no columns would be added), will search in sheetA for matching values in Part1 and Part2, and will do a search() like formula in Part3, where a cell in SheetB.Part3 is the search text, and SheetA.Part3 is the within text.


So,
**SheetB**
[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]45[/TD]
[TD]ASNDWUX45[/TD]
[TD]12000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]RL[/TD]
[TD]PUGBSCARL[/TD]
[TD]5000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]48[/TD]
[TD]ASNDWUX48[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQN5[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R8[/TD]
[TD]PUGBSCAR8[/TD]
[TD]5155[/TD]
[TD]1548[/TD]
[/TR]
</tbody>[/TABLE]



**SheetA**
[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]42,45,89[/TD]
[TD]ASNDWUX[/TD]
[TD]12000[/TD]
[TD]0[/TD]
[TD]Part1 and 2 matched, "45" was found in part3[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R4[/TD]
[TD]PUGBSCA[/TD]
[TD][/TD]
[TD][/TD]
[TD]part1 and 2 matched, no match part3[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]45,48,49[/TD]
[TD]ASNDWUX[/TD]
[TD]12000[/TD]
[TD]5000[/TD]
[TD]part1 and 2 matched, 2 matches in part3. 45 and 48 both found, get biggest value from SheetB
[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQ[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[TD]all parts matched[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]NDM[/TD]
[TD]R8[/TD]
[TD]PUGBSCA[/TD]
[TD]5155[/TD]
[TD]1548[/TD]
[TD]Part1 and Part3 matched, part2 not.[/TD]
[/TR]
</tbody>[/TABLE]



I am not sure if this can be done by formula, if so it should be an array type or something. I rather not use complicated formulas because there will be lots of data columns in SheetA to be recalculated thanks to changes in SheetB.


The only part where a SEARCH would be done would be Part3, because Part1 and Part2 and the Value-x will not have concatenated information.


How can I do this?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Zenru said:
Hello everybody,


I have two sheets with information in Excel, one (SheetB) where there are unique values like the one below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]WUX[/TD]
[TD]45[/TD]
[TD]ASNDWUX45[/TD]
[TD]12000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]RL[/TD]
[TD]PUGBSCARL[/TD]
[TD]5000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]WUX[/TD]
[TD]48[/TD]
[TD]ASNDWUX48[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQN5[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R8[/TD]
[TD]PUGBSCAR8[/TD]
[TD]5155[/TD]
[TD]1548[/TD]
[/TR]
</tbody>[/TABLE]




And I have another sheet (SheetA), with the same values as above, but:

  • They repeat
  • Some might have in part3 concatenated information like: 45, 4F, 65D, S


So, I need a macro that, when a value changed in a specific range in SheetB (range might grow in rows, no columns would be added), will search in sheetA for matching values in Part1 and Part2, and will do a search() like formula in Part3, where a cell in SheetB.Part3 is the search text, and SheetA.Part3 is the within text.


So,
**SheetB**
[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]45[/TD]
[TD]ASNDWUX45
[/TD]
[TD]12000[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]RL[/TD]
[TD]PUGBSCARL
[/TD]
[TD]5000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]48[/TD]
[TD]ASNDWUX48
[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQN5[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R8[/TD]
[TD]PUGBSCAR8[/TD]
[TD]5155[/TD]
[TD]1548
[/TD]
[/TR]
</tbody>[/TABLE]



**SheetA**
[TABLE="width: 500"]
<tbody>[TR]
[TD]Part1[/TD]
[TD]Part2[/TD]
[TD]Part3[/TD]
[TD]Concat[/TD]
[TD]Value-1[/TD]
[TD]Value-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]42,45,89[/TD]
[TD]ASNDWUX
[/TD]
[TD]12000[/TD]
[TD]0[/TD]
[TD]Part1 and 2 matched, "45" was found in part3[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]SCA[/TD]
[TD]R4[/TD]
[TD]PUGBSCA[/TD]
[TD][/TD]
[TD][/TD]
[TD]part1 and 2 matched, no match part3
[/TD]
[/TR]
[TR]
[TD]ASND[/TD]
[TD]QEE[/TD]
[TD]45,48,49[/TD]
[TD]ASNDWUX
[/TD]
[TD]12000[/TD]
[TD]5000[/TD]
[TD]part1 and 2 matched, 2 matches in part3. 45 and 48 both found, get biggest value from SheetB
[/TD]
[/TR]
[TR]
[TD]COMP[/TD]
[TD]ASQ[/TD]
[TD]N5[/TD]
[TD]COMPASQ[/TD]
[TD]1000[/TD]
[TD]5000[/TD]
[TD]all parts matched[/TD]
[/TR]
[TR]
[TD]PUGB[/TD]
[TD]NDM[/TD]
[TD]R8[/TD]
[TD]PUGBSCA
[/TD]
[TD]5155[/TD]
[TD]1548[/TD]
[TD]Part1 and Part3 matched, part2 not.
[/TD]
[/TR]
</tbody>[/TABLE]



I am not sure if this can be done by formula, if so it should be an array type or something. I rather not use complicated formulas because there will be lots of data columns in SheetA to be recalculated thanks to changes in SheetB.


The only part where a SEARCH would be done would be Part3, because Part1 and Part2 and the Value-x will not have concatenated information.


How can I do this?
Click to expand...

I don't understand the values in red in column Concat in both tables. Shouldn't they be the concatenation of ColumnA & ColumnB?
Are these values in red typos?
Could you clarify?

M.
 
Upvote 0
My bad, it was typo.
The concat column is a concatenation of values from ColumnA B and C in SheetB and only Column A and B in SheetA (because Column3 there is a concatenated strings of multiple values separated by a comma)
 
Last edited:
Upvote 0
Can you clarify which cells in which sheet you want this macro or formula to populate?
 
Upvote 0
I know you've asked for a solution using VBA but i think it's possible to use a non very complex formula.

My suggestion

SheetB

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Part1​
[/TD]
[TD]
Part2​
[/TD]
[TD]
Part3​
[/TD]
[TD]
Concat​
[/TD]
[TD]
Value-1​
[/TD]
[TD]
Value-2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ASND​
[/TD]
[TD]
QEE​
[/TD]
[TD]
45​
[/TD]
[TD]
ASNDQEE​
[/TD]
[TD]
12000​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
PUGB​
[/TD]
[TD]
SCA​
[/TD]
[TD]
RL​
[/TD]
[TD]
PUGBSCA​
[/TD]
[TD]
5000​
[/TD]
[TD]
7000​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ASND​
[/TD]
[TD]
QEE​
[/TD]
[TD]
48​
[/TD]
[TD]
ASNDQEE​
[/TD]
[TD]
0​
[/TD]
[TD]
5000​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
COMP​
[/TD]
[TD]
ASQ​
[/TD]
[TD]
N5​
[/TD]
[TD]
COMPASQ​
[/TD]
[TD]
1000​
[/TD]
[TD]
5000​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
PUGB​
[/TD]
[TD]
SCA​
[/TD]
[TD]
R8​
[/TD]
[TD]
PUGBSCA​
[/TD]
[TD]
5155​
[/TD]
[TD]
1548​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Observe that column D is the concatenation of columns A & Column B

SheetA

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Part1​
[/TD]
[TD]
Part2​
[/TD]
[TD]
Part3​
[/TD]
[TD]
Concat​
[/TD]
[TD]
Value-1​
[/TD]
[TD]
Value-2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ASND​
[/TD]
[TD]
QEE​
[/TD]
[TD]
42,45,89​
[/TD]
[TD]
ASNDQEE​
[/TD]
[TD]
12000​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
PUGB​
[/TD]
[TD]
SCA​
[/TD]
[TD]
R4​
[/TD]
[TD]
PUGBSCA​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ASND​
[/TD]
[TD]
QEE​
[/TD]
[TD]
45,48,49​
[/TD]
[TD]
ASNDQEE​
[/TD]
[TD]
12000​
[/TD]
[TD]
5000​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
COMP​
[/TD]
[TD]
ASQ​
[/TD]
[TD]
N5​
[/TD]
[TD]
COMPASQ​
[/TD]
[TD]
1000​
[/TD]
[TD]
5000​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
PUGB​
[/TD]
[TD]
SCA​
[/TD]
[TD]
R8​
[/TD]
[TD]
PUGBSCA​
[/TD]
[TD]
5155​
[/TD]
[TD]
1548​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Also in SheetA the column D is the concatenation of column A & column B

Array formula in E2 copied across to F2 and down
=IFERROR(LARGE(IF(SheetB!$D$2:$D$6=$D2,IF(ISNUMBER(SEARCH(SheetB!$C$2:$C$6,$C2)),SheetB!E$2:E$6)),1),"")
Ctrl+Shift+Enter

M.
 
Last edited:
Upvote 0
Zenru said:
I want to populate the Value-1… columns in SheetA from the sheetB.
Click to expand...
Thanks for the clarification.

Comment:
I don't think Marcelo has quite nailed it - yet at least. For example, as I understand it the values in SheetB can be changed so if SheetB cell C2 in Marcelo's example is changed from 45 to 4 the formula still returns 12000 for Value-1 even though 4 is not one of the values in C2 of SheetA.

This is my attempt.

Excel Workbook
ABCDEF
1Part1Part2Part3ConcatValue-1Value-2
2ASNDQEE45ASNDQEE120000
3PUGBSCARLPUGBSCA50007000
4ASNDQEE48ASNDQEE05000
5COMPASQN5COMPASQ10005000
6PUGBSCAR8PUGBSCA51551548
SheetB



Formula in E2 is copied across and down after adjusting the $100 values to be sure to be big enough for whatever size your data might become in SheetB.

Excel Workbook
ABCDEF
1Part1Part2Part3ConcatValue-1Value-2
2ASNDQEE42,45,89ASNDQEE120000
3PUGBSCAR4PUGBSCA
4ASNDQEE45,48,49ASNDQEE120005000
5COMPASQN5COMPASQ10005000
6PUGBSCAR8PUGBSCA51551548
SheetA
 
Upvote 0
Peter_SSs said:
Comment:
I don't think Marcelo has quite nailed it - yet at least. For example, as I understand it the values in SheetB can be changed so if SheetB cell C2 in Marcelo's example is changed from 45 to 4 the formula still returns 12000 for Value-1 even though 4 is not one of the values in C2 of SheetA.
Click to expand...

Peter, you are right - thank you for pointing out

The formula can be easily adjusted
E2 copied across to F2 and down
=IFERROR(LARGE(IF(SheetB!$D$2:$D$6=$D2,IF(ISNUMBER(SEARCH(","&SheetB!$C$2:$C$6&",",","&SUBSTITUTE($C2," ","")&",")),SheetB!E$2:E$6)),1),"")
Ctrl+Shift+Enter

By the way, a possible solution with VBA

Code: 
Sub aTest()
    Dim dic As Object
    Dim vDataB As Variant, lastRowA As Long, vDataA As Variant, vResult As Variant
    Dim i As Long, j As Long
    Dim spl As Variant, Max1 As Double, Max2 As Double, bFound As Boolean
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    With Sheets("SheetB")
        vDataB = .Range("C2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For i = LBound(vDataB) To UBound(vDataB)
            dic(vDataB(i, 2) & "|" & vDataB(i, 1)) = Array(vDataB(i, 3), vDataB(i, 4))
        Next i
    End With
        
    With Sheets("SheetA")
        lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        vDataA = .Range("C2:D" & lastRowA)
        .Range("E2:F" & lastRowA).ClearContents
        vResult = .Range("E2:F" & lastRowA)
        For i = LBound(vDataA) To UBound(vDataA)
            bFound = False
            Max1 = 0
            Max2 = 0
            spl = Split(Replace(vDataA(i, 1), " ", ""), ",")
            For j = LBound(spl) To UBound(spl)
                If dic.exists(vDataA(i, 2) & "|" & spl(j)) Then
                    bFound = True
                    Max1 = Application.Max(Max1, dic(vDataA(i, 2) & "|" & spl(j))(0))
                    Max2 = Application.Max(Max2, dic(vDataA(i, 2) & "|" & spl(j))(1))
                End If
            Next j
            If bFound Then
                vResult(i, 1) = Max1
                vResult(i, 2) = Max2
            End If
        Next i
        .Range("E2:F" & lastRowA) = vResult
    End With
End Sub

M.
 
Upvote 0
IFERROR(AGGREGATE(14,6,SheetB!E$2:E$100/(ISNUMBER(SEARCH(","&SheetB!$C$2:$C$100&",",","&$C2&","))*(SheetB!$D$2:$D$100=$D2)),1),"")

I dont understand where is $C2 and $D2 from. Are they from SheetA? I need to pull from SheetB to SheetA.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,108
Messages
6,170,143
Members
452,304
Latest member
Thelingly95

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top