Consulting

Results 1 to 13 of 13

Thread: Display values based on matching column data

  1. #1

    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
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    Dear Expert

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

    I describe the value to be placed for each row.

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

    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 
    
    
    Formatting tags added by mark007
    -I HAVE NO IDEA WHAT I'M DOING

  4. #4
    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?
    help.jpg
    -I HAVE NO IDEA WHAT I'M DOING

  5. #5
    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.
    -I HAVE NO IDEA WHAT I'M DOING

  6. #6
    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.
    Mytest1.xlsm
    What you've asked is possible - I just don't have time to resolve the 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 
    
    
    Formatting tags added by mark007
    -I HAVE NO IDEA WHAT I'M DOING

  7. #7
    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.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  8. #8
    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
     'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"
    
    
    Formatting tags added by mark007
    I will add the UCase Statements.

    Please further explain why row 6 on sheet Emp should have status 0
    -I HAVE NO IDEA WHAT I'M DOING

  9. #9
    Mytest.xlsm
    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 
    
    
    Formatting tags added by mark007
    -I HAVE NO IDEA WHAT I'M DOING

  10. #10
    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.
     
    
    
    Formatting tags added by mark007
     '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 
    [FONT=Verdana][/FONT] 
    
    
    Formatting tags added by mark007

    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Last edited by sarat; 11-14-2017 at 06:32 PM.

  11. #11
    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
    -I HAVE NO IDEA WHAT I'M DOING

  12. #12
    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

  13. #13
    -I HAVE NO IDEA WHAT I'M DOING

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •