Macro to Compare Two sheets, if Match *within* Cell then Update another Cell

[karimifarzan]

All,

I need to write a semi-complicated VB macro that accomplishes the following: It compares two worksheets (named CTP and SRTM). It uses comma separated entries in column B of the CTP sheet and attempts to find a corresponding match in column A of the SRTM sheet. If a match is found, it copies the corresponding entry in Column A of the CTP sheet to Column B of the SRTM sheet.

I know this may be hard to visualize so I uploaded a mock workbook and an example below to illustrate what needs to be done:

Example:
In the CTP Sheet, there are 2 columns. Column A contains a Test Step Number and Column B contains multiple comma separated Control IDs. The first Cell with Control ID entries contains: "IA.2.a, IA.2.b, IA.4.4, PL.2.1". The macro will first take "IA.2.a" and search through column A of the SRTM sheet for a match. If a match is found it will then populate Column B (the Test Step Number) with Column A of the original CTP sheet that had the Test Step Number. So in this case, the number '4' would be copied from the Column A of the CTP sheet into Column B of the SRTM sheet (next to the IA.2.a entry).

If there's any additional information I can provide I'd be happy to do so. I appreciate it if anyone could provide some general guidelines as I'm relatively new to VB.

Thanks.

[AB33]

It is not clear from you request if the match also includes the split of Column A in STMP sheet by dot, but this code does not assume so, i.e only splits up column B of CTP and tried to match column A of STMP. It is slow as it has to go through each split. Try it anyway

Please Login or Register  to view this content.

[karimifarzan]

AB33, thank you for posting the solution. It works flawlessly. The only thing that's missing is the code above processes only the first entry of Column B in the CTP Sheet (before the ',' split) and ignores everything after. i.e.- The cell (in column B in the CTP sheet) may have the following entries: "IA.2.A, IA.2.b, IA.4.4, PL.2.1" but only copies the corresponding value over for IA.2.A, so the step numbers are still blank for the remaining entries (IA.2.b, IA.4.4, PL.2.21).

You've provided most of the solution in your response though. I'll go ahead and try to figure out the multiple entries issue and post back once I have a fully working solution (unless you know a quick fix) and mark this as solved. Thanks again.

[AB33]

Okay!
Although the split looks for each item, for some unknown reason, it only matches the first split and could not find a match for subsequent splits, despite there are matches. Hmmm!
Will have a look and see if I can figure out the reason.