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

[Zenru]

Hello everybody,


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

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	WUX	45	ASNDWUX45	12000	0
PUGB	SCA	RL	PUGBSCARL	5000	7000
ASND	WUX	48	ASNDWUX48	0	5000
COMP	ASQ	N5	COMPASQN5	1000	5000
PUGB	SCA	R8	PUGBSCAR8	5155	1548

<tbody>
</tbody>

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**

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	QEE	45	ASNDWUX45	12000	0
PUGB	SCA	RL	PUGBSCARL	5000	7000
ASND	QEE	48	ASNDWUX48	0	5000
COMP	ASQ	N5	COMPASQN5	1000	5000
PUGB	SCA	R8	PUGBSCAR8	5155	1548

<tbody>
</tbody>

**SheetA**

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	QEE	42,45,89	ASNDWUX	12000	0	Part1 and 2 matched, "45" was found in part3
PUGB	SCA	R4	PUGBSCA			part1 and 2 matched, no match part3
ASND	QEE	45,48,49	ASNDWUX	12000	5000	part1 and 2 matched, 2 matches in part3. 45 and 48 both found, get biggest value from SheetB
COMP	ASQ	N5	COMPASQ	1000	5000	all parts matched
PUGB	NDM	R8	PUGBSCA	5155	1548	Part1 and Part3 matched, part2 not.

<tbody>
</tbody>

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?

 

[Zenru]

Bump - 1d

 

[Marcelo Branco]

Hello everybody,


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

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	WUX	45	ASNDWUX45	12000	0
PUGB	SCA	RL	PUGBSCARL	5000	7000
ASND	WUX	48	ASNDWUX48	0	5000
COMP	ASQ	N5	COMPASQN5	1000	5000
PUGB	SCA	R8	PUGBSCAR8	5155	1548

<tbody>
</tbody>

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**

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	QEE	45	ASNDWUX45	12000	0
PUGB	SCA	RL	PUGBSCARL	5000	7000
ASND	QEE	48	ASNDWUX48	0	5000
COMP	ASQ	N5	COMPASQN5	1000	5000
PUGB	SCA	R8	PUGBSCAR8	5155	1548

<tbody>
</tbody>

**SheetA**

Part1	Part2	Part3	Concat	Value-1	Value-2
ASND	QEE	42,45,89	ASNDWUX	12000	0	Part1 and 2 matched, "45" was found in part3
PUGB	SCA	R4	PUGBSCA			part1 and 2 matched, no match part3
ASND	QEE	45,48,49	ASNDWUX	12000	5000	part1 and 2 matched, 2 matches in part3. 45 and 48 both found, get biggest value from SheetB
COMP	ASQ	N5	COMPASQ	1000	5000	all parts matched
PUGB	NDM	R8	PUGBSCA	5155	1548	Part1 and Part3 matched, part2 not.

<tbody>
</tbody>

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?

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.

 

[Zenru]

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)

 

[Peter_SSs]

Can you clarify which cells in which sheet you want this macro or formula to populate?

 

[Marcelo Branco]

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

	A​	B​	C​	D​	E​	F​
1​	Part1​	Part2​	Part3​	Concat​	Value-1​	Value-2​
2​	ASND​	QEE​	45​	ASNDQEE​	12000​	0​
3​	PUGB​	SCA​	RL​	PUGBSCA​	5000​	7000​
4​	ASND​	QEE​	48​	ASNDQEE​	0​	5000​
5​	COMP​	ASQ​	N5​	COMPASQ​	1000​	5000​
6​	PUGB​	SCA​	R8​	PUGBSCA​	5155​	1548​
7​

<tbody>
</tbody>

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

SheetA

	A​	B​	C​	D​	E​	F​
1​	Part1​	Part2​	Part3​	Concat​	Value-1​	Value-2​
2​	ASND​	QEE​	42,45,89​	ASNDQEE​	12000​	0​
3​	PUGB​	SCA​	R4​	PUGBSCA​
4​	ASND​	QEE​	45,48,49​	ASNDQEE​	12000​	5000​
5​	COMP​	ASQ​	N5​	COMPASQ​	1000​	5000​
6​	PUGB​	SCA​	R8​	PUGBSCA​	5155​	1548​
7​

<tbody>
</tbody>

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.

 

[Zenru]

I want to populate the Value-1… columns in SheetA from the sheetB.

 

[Peter_SSs]

I want to populate the Value-1… columns in SheetA from the sheetB.

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
	A	B	C	D	E	F
1	Part1	Part2	Part3	Concat	Value-1	Value-2
2	ASND	QEE	45	ASNDQEE	12000	0
3	PUGB	SCA	RL	PUGBSCA	5000	7000
4	ASND	QEE	48	ASNDQEE	0	5000
5	COMP	ASQ	N5	COMPASQ	1000	5000
6	PUGB	SCA	R8	PUGBSCA	5155	1548
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
	A	B	C	D	E	F
1	Part1	Part2	Part3	Concat	Value-1	Value-2
2	ASND	QEE	42,45,89	ASNDQEE	12000	0
3	PUGB	SCA	R4	PUGBSCA
4	ASND	QEE	45,48,49	ASNDQEE	12000	5000
5	COMP	ASQ	N5	COMPASQ	1000	5000
6	PUGB	SCA	R8	PUGBSCA	5155	1548
SheetA

 

[Marcelo Branco]

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.

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

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.

 

[Zenru]

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.