Posts by pete2020

    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

    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

    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

    I am very thankful for this awesome blog.


    Just similar to this Issue but adding extra column Match and Inserting addition Columns


    I am Looking for a VBA Macro which would save my Plenty of man hours.


    In sheet1 Column A (Primary category) and column B (Course) is matching with Sheet 2 with Column A and Column B (Keywords Partial Match) then Column B matched Keywords along with all adjacent rows to be populated in sheet 1 with comma Delimiter


    Criteria

    =======

    1.Column A Values in sheet 2 - should "exact Match" in with Sheet 1 Column A

    2.Column B Values in Sheet 2 - Should "Partial Match" against each cell in sheet 1 Column A

    3.Matching of all keywords should be compared in each cell level of column B of sheet 1

    4.Case sensitive to be ignore both the columns

    5.All the adjacent rows to be appended to the respective row in Column A with comma delimiter

    6.Please delete duplicate and copy only unique values at each level of Populating the data from sheet 2 to Sheet 1

    4.My data was around 100k Rows in Sheet 1 and my keywords range in sheet2 would be 400 rows


    Thank You So much for all your patience and helping many members