Скачать или смотреть How to Use a VBA Macro to Find Partial Matches in Two Excel Sheets

Learn how to create a VBA macro that finds partial matches between two columns on different sheets and copies values accordingly in Excel.
---
This video is based on the question https://stackoverflow.com/q/68457698/ asked by the user 'Elmar81' ( https://stackoverflow.com/u/16488963/ ) and on the answer https://stackoverflow.com/a/68458111/ provided by the user 'SJR' ( https://stackoverflow.com/u/7008044/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA macro to find partial match in 2 columns on 2 sheets and copy value from sheet 2 to sheet 1

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering VBA: Finding Partial Matches in Excel

If you've ever found yourself struggling to match items between two columns in Excel, you're not alone. Many users need to compare data across sheets but aren’t sure how to approach it, especially when only partial matches are necessary.

For instance, you may want to find specific URLs in one sheet that only have parts of them found in another sheet. This post will walk you through a solution using VBA (Visual Basic for Applications), allowing you to automate this process effectively.

The Challenge

Consider the following scenario:

Sheet 1 contains URLs in Column B that look like this: https://website/news/2021/title-of-news-article.

Sheet 2 holds partial URLs (only the last part) in Column D, such as title-of-news-article, with corresponding values in Column E.

Your objective is to compare Column D (Sheet 2) with Column B (Sheet 1) and, upon finding a match, copy the corresponding value from Column E (Sheet 2) into Column F (Sheet 1).

The VBA Solution

To achieve this functionality, you can use the following VBA macro:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Macro

Setting Up the Worksheets: The macro starts by defining the two worksheets you are working with, sheet1 and sheet2.

Loop through Sheet 2: The For Each loop iterates through each cell in Column D of Sheet 2.

Finding Partial Matches:

The Find method is utilized to search for partial matches of each element in Column B of Sheet 1.

We specify the search with Lookat:=xlPart to enable partial matching.

Copying Values: If a match is found in Sheet 1:

The corresponding value from Column E of Sheet 2 is copied into Column F of Sheet 1.

Optimizing Performance: Application.ScreenUpdating is set to False before running the macro to improve performance, and then turned back on after execution.

Conclusion

If you're new to VBA, this macro might seem complex, but with a little practice, you'll find it to be a powerful tool for automating Excel tasks. Use the solution above to handle partial matches and efficiently manage your data across multiple sheets. Remember, practice makes perfect, so don’t hesitate to experiment with different scenarios!

With this knowledge, you can now easily compare and manipulate data in Excel, saving you time and effort. Happy coding!