VBA Code - Compare and populate Keywords with comma delimiter

  • I would be more Thankful if you Provide a solution for the Issue which is taking a longer time to do Manually.


    Based on Column A the column B is sheet 2 to be matched against sheet 1 Column B

    Match Criteria

    -------------

    Sheet2 Column A should Match with Sheet 1 of Column A - Exact Match

    Sheet 2 Column B should match aganist column B and C in Sheet 1 - partial Match

    Sheet 2 Column B and C has multiple variations of Keywords with comma separated

    Match with Plural words for the each Keyword: for Example as for business it should match “Es”

    If None of the Keywords matched it should display as Not found

    Match should not be case sensitive

    All the matched pairs should be populated in Column C and Column D of sheet 1 with comma delimiter

    All the adjacent rows From Column D to L of sheet 2 be populated to sheet 1 with comma separated (Only Populate Unique Words with comma delimiter)

    While comparing column B key words it should consider exact space match " Co mplete" or "Com plete"



    I need to compare 100k rows in sheet1 with almost 400 pairs of keywords of sheet 2

    Please find the attached excel

  • Possibly...?

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Three assumptions were made:

    1. The data is in Worksheets named "Sheet1" and "Sheet2"

    2. The data in both worksheets begin in the top left cell "A1"

    3. The code is placed in a standard code module in the same workbook as the worksheets


    If these assumptions are good, then I would need to know the complete error description.

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Thank You for helping out.


    The Code is partially working fine, But found few flaws to fix in the macro


    1. Macro HAS TO Excluding the keywords in Column C sheet 2 while searching in column A

    (Please Check Row 2 in sheet 2 - comparing aganist Row 4 and 5) - Both are different -Actually Row 4 does not fall under Retargeting KW

    2. Macro HAS TO considering plurals for keywords for Column B keywords

    (Given the code model below to indentify plurals for keywords)

    3. Macro HAS TO considering Multiple words (mentioned in comma) in column B

    (ex: Chat Bots, Chatbots (Row 4 in sheet2 should match for Row 2 and Row 6 in sheet 1)

    4.Macro has to compare across the full range of respective column data in sheet 1 and 2


    For Plurals search criteria please modify the below condition this as per our keyword search criteria


    For Each X In d.Keys

    For Each z In ary

    If InStr(1, X, " " & z & " ", 1) + InStr(1, X, " " & z & "s ", 1) + InStr(1, X, " " & z & "es ", 1) = 0 Then

    d.Remove X: Exit For

    End If

    Next

    Next

  • Try this version...

    I could not incorporate your code for including plurals - there is no definition for "ary"

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Dangelor, Thank You for modifying the macro.


    The Macro needs some fixes

    1. The Sheet2 Column E data is not Populated in sheet1 with comma seperated - based on keyword match

    2. The excluded words are not considered while find and match

    3. In sheet 2 , Column B comma seperated keywords are not functioning well within the macro.


    Please run on the sample file