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?

 

[Peter_SSs]

Yes, they are from SheetA.

You said ...

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

.. which to me implied two things ..
i) You wanted formulas for the red columns below, and
ii) Therefore you must already have the information in the blue columns
.. in the SheetA that you originally posted.

That is why I used C2 and D2 (from SheetA) in my formula - because I believed they were already there.

If C2 (Part3) and D2 (Concat) are not already there, what does this description mean as it refers to the Part3 column "Part1 and 2 matched, "45 was found in part3"?




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

 

[Zenru]

Hello, it was incredible rude of me to not give an update on this. Thanks everyone for the input. I went the VBA way, I used and modified Marcelo's code a bit and it worked perfecttly.