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:

[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?

 

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

 

[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.