VBA to insert column based on condition for matching col data

[tikistat]

Hi,

I want to insert a new column at the end into child sheet and rename as "My Output".
In that column, I need to populate the flag 1,2, 3, 0 based on data matches between parent and child sheet. I have mentioned the criteria within the spreadsheet.

Attaching sheet for reference

The product searching should be based on pattern matching. It means if product name as 'Paracetamol' in child sheet is searching for the product column in master sheet and if the name found to be paracetamol syrup, then it should be considered as matching.( as part of it is matching). Similar condition applicable in vice versa

Could anyone please help me.

[PCI]

Can you give more details about the values 0,1,2 etc ...
For example why to use value 2 for Diovan and Paracet ?

[tikistat]

Thanks PCI for looking into my concern.

I want to insert new column at the end of row and populate flag like 1,2, 0 based on below conditions
Flag
1 means if product (pattern match), route and country matches between child and parent
2 means if product (pattern match) and country matches between child and parent where as route is blank or no value
3 means if product (pattern match) and country matches between child and parent where as route is not matching
0- mismatch

Thanks in advance

[PCI]

What do you mean by "pattern match" ??

[tikistat]

Its like the whole text from child sheet is matching with either full text or part of the text from master sheet (like operator)
e.g "Paracetamol" from child sheet is matching with "Paracetamol syrup" which can be considered as full match.

If you check the cell J2 value, it should be populated as value '1' because all three columns contain same text i.e.
child sheet parent sheet Flag
product route country Product route country
paracetamol oral US Paracetamol syrup oral US 1 (as it is matching)


Please check column J where I kept desired output

[PCI]

See the first piece of code for flag 1.
Because the result is not the same some clarification is needed

Option Explicit

Sub Treat()
Dim ChildWS As Worksheet
Dim MasterWS As Worksheet
Dim LR  As Long
Dim LC  As Integer
Dim WkRg As Range
Dim Rg  As Range
Dim F
Dim FAdd  As String
Dim AAA, BBB, CCC, DDD
Dim RR, RRR
    Set ChildWS = Sheets("Child")
    Set MasterWS = Sheets("Master")
    With MasterWS
        Set WkRg = Range(.Cells(2, "C"), .Cells(2, "C"))
    End With
'--- Prepa
    With ChildWS
        LR = .Cells(Rows.Count, "A").End(3).Row
        LC = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, LC + 1) = "My Output"
        Range(.Cells(2, LC + 1), .Cells(LR, LC + 1)) = 0
    End With


    With ChildWS
        For Each Rg In Range(.Cells(2, "F"), .Cells(Rows.Count, "F").End(3))
            If (Len(Rg) <> "") Then
                Set F = WkRg.Find(Rg.Value, LookIn:=xlValues, LookAt:=xlPart)
                If (Not F Is Nothing) Then
                    FAdd = F.Address
                    Do
'---                    Flag 1
                        If ((.Cells(F.Row, "H") = F.Offset(0, 3)) And (.Cells(F.Row, "C") = F.Offset(0, 4))) Then _
                            .Cells(Rg.Row, LC + 1) = 1
                            
'---                    Flag 2
                            
                            
                        Set F = WkRg.FindNext(F)
                    Loop While (F.Address <> FAdd)
                End If
            End If
        Next
    End With
End Sub

[tikistat]

Thank You so much PCI. I am currently testing it in my project.

Could you please guide what code should be written to populate flag 2 and 3 (PID should be taken as group variable as the matching must be done within the group).

Thanks once again for your great help and support

[PCI]

Before to finish Flag 2 and 3, I would like to be sure that flag 1 code is working properly:
You add a specification about PID ...!

[PCI]

See here a remake taking care of PID and flag2...
Result does not fit to your values ...!

Option Explicit

Sub Treat()
Dim ChildWS As Worksheet
Dim MasterWS As Worksheet
Dim LR  As Long
Dim LC  As Integer, I As Integer, II As Integer, ISt As Integer
Dim WkRg As Range, ProdRg As Range
Dim Rg  As Range
Dim F
Dim FAdd  As String
Dim AAA, BBB, CCC, DDD
Dim RR, RRR
Const PidCol As String = "B"
Const CtyCol As String = "C"
Const ProdCol As String = "F"
Const RoutCol As String = "H"

    Set ChildWS = Sheets("Child")
    Set MasterWS = Sheets("Master")
    With MasterWS
        Set WkRg = Range(.Cells(2, "C"), .Cells(2, "C"))
    End With
'--- Prepa
    With ChildWS
        LR = .Cells(Rows.Count, "A").End(3).Row
        LC = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, LC + 1) = "My Output"
        Range(.Cells(2, LC + 1), .Cells(LR, LC + 1)) = 0
    End With

    With ChildWS
        LR = .Cells(Rows.Count, "A").End(3).Row
        ISt = 2
        For I = 2 To LR
            If (.Cells(I + 1, PidCol) <> .Cells(I, PidCol)) Then
                Set ProdRg = Range(.Cells(ISt, ProdCol), .Cells(I, ProdCol))
                ProdRg.Select
                For Each Rg In ProdRg
                    If (Len(Rg) <> "") Then
                        Set F = WkRg.Find(Rg.Value, LookIn:=xlValues, LookAt:=xlPart)
                        If (Not F Is Nothing) Then
                            FAdd = F.Address
                            Do
        '---                    Flag 1
                                If ((.Cells(F.Row, CtyCol) = F.Offset(0, 4)) And (.Cells(F.Row, RoutCol) = F.Offset(0, 3))) Then
                                    .Cells(Rg.Row, LC + 1) = 1
                                End If
                                    
        '---                    Flag 2
                                If ((.Cells(F.Row, CtyCol) = F.Offset(0, 4)) And Len(.Cells(F.Row, RoutCol) = 0)) Then
                                    .Cells(Rg.Row, LC + 1) = 2
                                End If
                                Set F = WkRg.FindNext(F)
                            Loop While (F.Address <> FAdd)
                        End If
                    End If
                Next Rg
                ISt = I + 1
            End If
        Next I
    End With
End Sub

[tikistat]

I am making the data as simple so that it will be easier to explain for every rows.
Note:- Data from column D and C means product Paracetamol, Nimusulid and caffeine are sold in US available in A1.doc
The A2 doc describes product Diovan and asthafen are sold in both UK and India. so while matching the product, any of the combination should be considered like UK-diovan or UK_Asthafen or
India_diovan or India-Asthafen). See row number RW6 for reference.

RW2:- Flag should be 1 as the data (US-Paracetamol-Oral) from child sheet matches with Master sheet (US-Paracetamol syrup-Oral)
RW3:- Flag should be 0 as the data mismatch
RW4:- Flag should be 0 as the data mismatch
RW5:- Flag should be 2 as the data (UK-Diovan) from child sheet matches with Master sheet (UK-Diovan) and route column data is blank.
RW6:- Flag should be 1 as the data (UK-Asthafen-sc) from child sheet matches with Master sheet (UK-Asthafen-sc)because this Asthafen is sold in two different country
RW7:- Flag should be 0 as the product mismatch.
RW8:- Flag should be 2 as the data (China-Atorvastatin) from child sheet matches with Master sheet (China-Atorvastatin) as the product is sold in both china and japan
RW9:- Flag should be 1 as the data (China-Cycolpam Injection-sc) from child sheet matches with Master sheet (China-Cycolpam-sc)
RW10:- Flag should be 0 as the product match but other two column value not matching or null value

Column A should be considered as group variable as data is document specific (like A1.doc, A2.doc etc.)

Please check the attached sheet for your reference.

[PCI]

It is a bit confusing:
RW6:- Flag should be 1 as the data (UK-Asthafen-sc) from child sheet matches with Master sheet (UK-Asthafen-sc)because this Asthafen is sold in two different country
=> It is not UK

RW9:- Flag should be 1 as the data (China-Cycolpam Injection-sc) from child sheet matches with Master sheet (China-Cycolpam-sc)
=> It means we have to search Cycolpam into Madter sheet but why not Injection ???

[PCI]

See attached the last revision
In sheet "Info" is marked in Red when it must be clarified

[tikistat]

Thanks once again PCI for your continuous effort in looking into my query.

RW6:- Flag should be 1 as the data (UK-Asthafen-sc) from child sheet matches with Master sheet (UK-Asthafen-sc)because this Asthafen is sold in two different country
=> It is not UK

Clarificaton=> It means as per A2 document, the product ASTHAFEN is sold in both countries i.e. UK and India . Similar for Diovan product also.
Therefore the macro should work taking both countries for searching in Master file in multiple combination like (UK-Diovan, UK-Asthafen, UK-Asthafen,India-Asthafen)


RW9:- Flag should be 1 as the data (China-Cycolpam Injection-sc) from child sheet matches with Master sheet (China-Cycolpam-sc)
=> It means we have to search Cycolpam into Master sheet but why not Injection ???

Clarificaton=> Yes you are correct. As cyclopam is available in market in injection or tablet format, my requirement is to search the active ingredient.
Therefore the macro should either match the complete text (i.e. Cycolpam Injection) or if it is not found, search only the first word(i.e. cyclopam).

I hope this is fine now.

Thanks once again

[tikistat]

Hello PCI

I am testing the program in my project sheet (attachment for reference).
May I request you to check the flag once again for 1 and 2 as it is currently not populating.

For RW8, the macro should populate 2 as the data (France-GLIBENCLAMIDE) from child sheet is matching with data (France-GLIBENCLAMIDE) from master sheet where as forumulation field is blank.
For RW7, it should populate 1.

Please check once again.

Thanks in advance