Display values based on matching column data

Hello,

From the attached sheet, I want to insert one column automatically in Emp sheet, rename as 'Merge_status' after data end and populate the value as 1,2 and 0 based on matching between two sheets emp and empmaster.

Grouping of data should be based on first column.


Note that Empdept is present in more than one country (i.e. 102 for UK and Japan). Therefore matching should be considered as UK-Rakesh, UK-Nilesh, UK-Kikesh, Japan-Rakesh, Japan-Nilesh, Japan-Kikesh. Example as row number 6


1)Merge_status will be 1 when any of the country (may be more than), ename and job column from Emp sheet is matching with country, ename and job column of EmpMaster sheet.
e.g.
For row number 2 where data (US-Manish-Clerk) is matching with data (US-ManishKumar-Clerk)--> Merge_status is 1
For row number 3 where data (US-Kiran Kumar-Clerk) is matching with data (US-Kiran-Clerk)--> Merge_status is 1
For row number 6 where data (Japan-Rakesh-Manager) is matching with data (Japan-Rakesh-Manager)--> Merge_status is 1 as different emp with same name may work in UK and Japan.


2)Merge_status will be 2 when any of the country (may be more than) and ename column from Emp sheet is matching with country and ename column of EmpMaster sheet where as job column data is blank
e.g.
For row number 4 where data (US-Harish) from emp sheet is matching with data (US-Harish) from empmaster sheet but job field is blank in emp sheet --> Merge_status is 2
For row number 8 where data (Japan-Kikesh) from emp sheet is matching with data (Japan-Kikesh) from empmaster sheet but job field is blank in emp sheet --> Merge_status is 2


3)Other condition should be considered as zero as merge_status.


eg
For row number 5, even though data (US-Nagesh) from emp sheet is matching with data (US-Nagesh) from empmaster sheet but job field was not matching between two sheet --> Merge_status is 0
For row number 7, even though data (UK-Nilesh) from emp sheet is matching with data (UK-Nilesh) from empmaster sheet but job field was not matching between two sheet --> Merge_status is 0

Dear Expert

Can you please help me to populate the value in a column.

I describe the value to be placed for each row.

ok that's a lot. ill can help you in pieces.
This will add the column if it doesn't already exist.

Code:

---

Sub AddMerge_StatusColumn()
    i = 1
    Do While Sheet1.Cells(1, i).Value <> ""
          If Sheet1.Cells(1, i).Value = "Merge_status" Then Exit Sub
    i = i + 1
    Loop
    Sheet1.Cells(1, i).Value = "Merge_status"
End Sub

---

Is it wrong to assume that:
while you did not provide all of the listed data below,
The data that I filled in is not wrong?
Attachment 20945

I solved this last night and was just about to post it - right before I accidently closed the file without saving. let me re write it.

This will do 90% of what you asked for.
The problem is you have a crappy data set.
I have reattached the file with the working code and Highlighted all of the instances where I had to inference the data.
Attachment 20955
What you've asked is possible - I just don't have time to resolve the code.

Code:

---

Public S1cMerge_status

Sub Main()
    AddColumn
    DetermineStatus
End Sub

Private Sub AddColumn()
    i = 1
    Do While Sheet1.Cells(1, i).Value <> ""
        If Sheet1.Cells(1, i).Value = "Merge_status" Then
          S1cMerge_status = i
          Exit Sub
        End If
        i = i + 1
    Loop
    Sheet1.Cells(1, i).Value = "Merge_status"
    S1cMerge_status = i
End Sub

Private Sub DetermineStatus()
    S1cKEY = 1
    S1cCountry = 3
    S1cEname = 4
    S1cJob = 6
   
    S2cKEY = 1
    S2cCountry = 3
    S2cEName = 4
    S2cJob = 5

    j = 2
    Do Until Sheet1.Cells(j, S1cKEY).Value = ""
          If Sheet1.Cells(j, S1cCountry).Value <> "" Then S1vCountry = Sheet1.Cells(j, S1cCountry).Value Else S1vCountry = "EMPTY"
          If Sheet1.Cells(j, S1cEname).Value <> "" Then S1vEName = Sheet1.Cells(j, S1cEname).Value Else S1vEName = "EMPTY"
          If Sheet1.Cells(j, S1cJob).Value <> "" Then S1vJob = Sheet1.Cells(j, S1cJob).Value Else S1vJob = "EMPTY"
          Sheet1.Cells(j, S1cMerge_status).Value = ""
         
          k = 2
          Do Until Sheet2.Cells(k, S2cKEY).Value = ""
         
          'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"
              If (S1vEName <> "EMPTY" And S1vJob <> "EMPTY") Then
                    If InStr(1, Sheet2.Cells(k, S2cEName).Value, S1vEName) <> 0 Then
                        If InStr(1, Sheet2.Cells(k, S2cJob).Value, S1vJob) <> 0 Then
                              Sheet1.Cells(j, S1cMerge_status).Value = 1
                        End If
                    End If
                    'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
                    '    & S1vEName & " : " & Sheet2.Cells(k, S2cEname).Value & vbNewLine _
                    '    & S1vJob & " : " & Sheet2.Cells(k, S2cJob).Value
              End If
         
          'STATUS 2 - Country AND EName MATCH AND Job IS "EMPTY"
                If Sheet1.Cells(j, S1cMerge_status).Value <> 1 Then
                    If (S1vCountry <> "EMPTY" And S1vEName <> "EMPTY" And S1vJob = "EMPTY") Then
                        If InStr(1, Sheet2.Cells(k, S2cCountry).Value, S1vCountry) <> 0 Then
                              If InStr(1, Sheet2.Cells(k, S2cEName).Value, S1vEName) <> 0 Then
                                  Sheet1.Cells(j, S1cMerge_status).Value = 2
                              End If
                        End If
                        'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
                        '    & S1vCountry & " : " & Sheet2.Cells(k, S2cCountry).Value & vbNewLine _
                        '    & S1vEName & " : " & Sheet2.Cells(k, S2cEName).Value & vbNewLine _
                        '    & S1vJob
                    End If
              End If
         
          k = k + 1
          Loop
         
          'STATUS 0 - IF NOT STATUS 1 OR 2
              If (Sheet1.Cells(j, S1cMerge_status).Value <> 1 And Sheet1.Cells(j, S1cMerge_status).Value <> 2) Then Sheet1.Cells(j, S1cMerge_status).Value = 0
    j = j + 1
    Loop
End Sub

---

Thank you minicus for providing the solution.
Is it possible to add upper() to the macro as I added one row from the latest attached excel file and it is not flagging as 1 due to mismatch in case.

Now, I have added all countries to all rows (please check the attached sheet) and then run the macro to test it. It is working fine except row number 6 which should be flag as 0 in stead of 1.

May I request to check once.

It would probably be wise to use the UCase function on both the string and the sub-string of the InStr functions.
I ran the 'Main' macro and it functioned as designed.

Row 6 on sheet Emp contains UK-Rakesh-Manager.
and it is matching to
Row 8 on sheet EmpMaster containing Japan-Rakesh-Manager.
the status on sheet Emp is set to 1 because

Code:

---

'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"

---

I will add the UCase Statements.

Please further explain why row 6 on sheet Emp should have status 0

Attachment 20959

Code:

---

Public S1cMerge_status
 
Sub Main()
    AddColumn
    DetermineStatus
End Sub
 
Private Sub AddColumn()
    i = 1
    Do While Sheet1.Cells(1, i).Value <> ""
        If Sheet1.Cells(1, i).Value = "Merge_status" Then
            S1cMerge_status = i
            Exit Sub
        End If
        i = i + 1
    Loop
    Sheet1.Cells(1, i).Value = "Merge_status"
    S1cMerge_status = i
End Sub
 
Private Sub DetermineStatus()
    S1cKEY = 1
    S1cCountry = 3
    S1cEname = 4
    S1cJob = 6
   
    S2cKEY = 1
    S2cCountry = 3
    S2cEName = 4
    S2cJob = 5
   
    j = 2
    Do Until Sheet1.Cells(j, S1cKEY).Value = ""
        If Sheet1.Cells(j, S1cCountry).Value <> "" Then S1vCountry = UCase(Sheet1.Cells(j, S1cCountry).Value) Else S1vCountry = "EMPTY"
        If Sheet1.Cells(j, S1cEname).Value <> "" Then S1vEName = UCase(Sheet1.Cells(j, S1cEname).Value) Else S1vEName = "EMPTY"
        If Sheet1.Cells(j, S1cJob).Value <> "" Then S1vJob = UCase(Sheet1.Cells(j, S1cJob).Value) Else S1vJob = "EMPTY"
        Sheet1.Cells(j, S1cMerge_status).Value = ""
       
        k = 2
        Do Until Sheet2.Cells(k, S2cKEY).Value = ""
           
            'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"
            If (S1vEName <> "EMPTY" And S1vJob <> "EMPTY") Then
                If InStr(1, UCase(Sheet2.Cells(k, S2cEName).Value), S1vEName) <> 0 Then
                    If InStr(1, UCase(Sheet2.Cells(k, S2cJob).Value), S1vJob) <> 0 Then
                        Sheet1.Cells(j, S1cMerge_status).Value = 1
                    End If
                End If
                'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
                '    & S1vEName & " : " & Sheet2.Cells(k, S2cEname).Value & vbNewLine _
                '    & S1vJob & " : " & Sheet2.Cells(k, S2cJob).Value
            End If
           
            'STATUS 2 - Country AND EName MATCH AND Job IS "EMPTY"
            If Sheet1.Cells(j, S1cMerge_status).Value <> 1 Then
                If (S1vCountry <> "EMPTY" And S1vEName <> "EMPTY" And S1vJob = "EMPTY") Then
                    If InStr(1, UCase(Sheet2.Cells(k, S2cCountry).Value), S1vCountry) <> 0 Then
                        If InStr(1, UCase(Sheet2.Cells(k, S2cEName).Value), S1vEName) <> 0 Then
                            Sheet1.Cells(j, S1cMerge_status).Value = 2
                        End If
                    End If
                    'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
                    '    & S1vCountry & " : " & Sheet2.Cells(k, S2cCountry).Value & vbNewLine _
                    '    & S1vEName & " : " & Sheet2.Cells(k, S2cEName).Value & vbNewLine _
                    '    & S1vJob
                End If
            End If
           
            k = k + 1
        Loop
       
        'STATUS 0 - IF NOT STATUS 1 OR 2
        If (Sheet1.Cells(j, S1cMerge_status).Value <> 1 And Sheet1.Cells(j, S1cMerge_status).Value <> 2) Then Sheet1.Cells(j, S1cMerge_status).Value = 0
        j = j + 1
    Loop
End Sub

---

I sincerely appreciate all the hard work in looking into my query.
I have added one more row to test the macro result (mytest.xlsm) but it is still not working.
Here the country is not matching between two sheets on Rownum 14 but it is still populating flag as 1.

It means Emp sheet contain data as (France Allopurinol Oral) where as Empmaster contain data as (Argentina ALLOPURINOL+COLCHICINE aGRANULES FOR ORAL SOLUTION) which is a complete mismatch and it should be displayed flag as zero.

Note

Flag 1= if country, emp and job matches between two sheets and this three variables are not blank
Flag 2= if country, emp matches between two sheets and job is blank

Please check once.

I try to change the code and all flag 1 change to zero. I am wrong.

Code:

---

---

Code:

---

'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"
            If (S1vEName <> "EMPTY" And S1vCountry <> "EMPTY" And S1vJob <> "EMPTY") Then
                If InStr(1, UCase(Sheet2.Cells(k, S2cEName).Value), S1vEName) <> 0 Then
                    If InStr(1, UCase(Sheet2.Cells(k, S2cJob).Value), S1vJob) <> 0 Then
                    If InStr(1, UCase(Sheet2.Cells(k, S2cCountry).Value), S1cCountry) <> 0 Then
                        Sheet1.Cells(j, S1cMerge_status).Value = 1
                    End If
                    End If
                End If
                'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
                '    & S1vEName & " : " & Sheet2.Cells(k, S2cEname).Value & vbNewLine _
                '    & S1vJob & " : " & Sheet2.Cells(k, S2cJob).Value
            End If

---

I cannot match some of the data all of the time - and all of the data some of the time.
In actuality, the code is finding an exact match - it is looking for the string 'ORAL' in the string "GRANULES FOR ORAL SOLUTION".
It is there. I have to use this method because you are trying to match things like 'Kiran' with 'Kiran Kumar'


My assumption this whole time has been that the column with green fill is the desired output.
If this is the case then 'Country' does not matter when it comes to 'Flag 1' cases.
If it can be any country then why check country?

Based on the rules that are set up and working for the first 13 rows of data; row 14 should be flagged as 1 not 0

Thank you Mincus for your kind word.

My apology for understanding the concept wrongly. Whatever you said , that is correct and you did the impossible task to make it possible to match things like 'Kiran' with 'Kiran Kumar'
Still, Please check the first post where I have mentioned the criteria to populate the flag 1.
Merge_status will be 1 when any of the country (may be more than), ename and job column from Emp sheet is matching with country, ename and job column of EmpMaster sheet.

There is a chances that only one emp can work only in one empdept. Therefore I kept that record for my testing to check matching between two sheets data.

Apology once again for inconvenience

Attachment 20962