Consulting

Results 1 to 13 of 13

Thread: Display values based on matching column data

  1. #1
    VBAX Regular
    Joined
    Jun 2012
    Posts
    29
    Location

    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

  2. #2
    VBAX Regular
    Joined
    Jun 2012
    Posts
    29
    Location
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    - I HAVE NO IDEA WHAT I'M DOING

  4. #4
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    - I HAVE NO IDEA WHAT I'M DOING

  7. #7
    VBAX Regular
    Joined
    Jun 2012
    Posts
    29
    Location
    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

  8. #8
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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"
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    - I HAVE NO IDEA WHAT I'M DOING

  10. #10
    VBAX Regular
    Joined
    Jun 2012
    Posts
    29
    Location
    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.
    
    
    '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

    Attached Files Attached Files
    Last edited by sarat; 11-14-2017 at 06:32 PM.

  11. #11
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    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
    VBAX Regular
    Joined
    Jun 2012
    Posts
    29
    Location
    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
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    234
    - 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
  •