Macro to Find a info in more than one column Solved/Closed

Question

Hi,  
I need a help to match values in a column from other two columns.  

I have two columns with some values on them.  
I have another column [third] with some more values in it.  

The third column is a master list which contains the value of first two columns.  

Kindly suggest a query or a macro to match the and flag column Founr as "Matched".  

The help would be highly appreciated.  
The Example"  
ColoumnA --ColumnB--ColumnC--ColumnD  
RHDLIB10 --HGNNID10--RHDLIB10--Matched  
EMBATV10--EMBPRY10--EMBATV10--Matched  
RHPANP10--EMBWAA10--RHPANP10--Matched  
DEXDMW10--EMBMVN10--DEXDMW10--Matched  
RHDWKN10--EMPRMP10--RHDWKN10--Matched  
 --DEXRLN10--HGNNID10--Matched  
 --EMBNPA10--EMBPRY10--Matched  
 --EMBLEW10--EMBWAA10--Matched  
      --EMBMVN10--Matched  
      --EMPRMP10--Matched  
      --DEXRLN10--Matched  
      --EMBNPA10--Matched  
      --EMBLEW10--Matched  
I Tried the followint query but it is working to match only one column at a time. It is not support matching on column/Row Range.   

=IF(ISERROR(MATCH(c2,a$17:a$34,0)),"","Complete")  
=IF(ISERROR(MATCH(c2,b$17:b$34,0)),"","Complete")  

Thank you and Best Regards - AJ

rizvisa1 · Answer

Your data look very confusing. From what I understood you said that you want to see that column C is master ? It does not look to me

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site  and post back here the link to allow better understanding of how it is now and how you foresee.

AJ · Answer

Dear Rizvisa1, 
Please refer to the following link 
[ https://authentification.site/files/22314281/Daily_Running_Total_Dashboard.xls ] 
password shown over there is [ neribajikova ]. 

Kindly let me know if this helps. 

In this excle file, the master information is on Column # [Z]
The source Columns are on [D17 to X40].
I would like the matching result on Column [AF].

Best Regards - AJ

rizvisa1 · Answer

AJ now can you explain your issue. Use the sample sheet to explain what you seek

rizvisa1 · Answer

AJ
I looked at your sheet "May" and none of the value in Z exist in D17:X40. Are you sure you have the right file ? or am i  missing some thing here?

AJ · Answer

Ok,
From Column [D17] thru [X40], I have a list of batches completed daily , listed over here (across month).
The Column [Z] contains list of batches expected for the month.

Now the help I am seeking from you is to update a status of a Batch in Column [AF] as "Complete" based on their availability/appearance on Column [D17] thru [X40]. 

Note: 
1. I have noticed that none of the books listed on Column [D], [E] and [F] are listed in Column [Z], hope you wont'n mind to add any batch from [Z] to column [D] so that you can test the results. - Apologize for not sending the correct data.

2. The merge cells could should create a problem, if this affects a macro or a query, then I can remove the merge and duplicate the information in the actual data. - Please advise.

Kindly let me know if more details are required.

Thank you and Best Regards - AJ

rizvisa1 · Answer

Add this function

Function isPresent(lookfor As String, searchRange As Range) As Long
Dim lThisRow As Long

    On Error Resume Next
    
        lThisRow = searchRange.Find(lookfor, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    On Error GoTo 0
    
    isPresent = lThisRow
    
End Function



then instead of match, use this function

as 
=IF(isPresent(Z2, D17:X40)>0, "Complete", "")

rizvisa1 · Answer

Ok try this. It works in 97 so it must work in 2000 too. 

You would call this function as  

=IF(isMatchPresent(Z2, D$17:X$40)>0, "Complete", "") 

Function isMatchPresent(lookfor As String, searchRange As Range) As Variant 
Dim sStartRange As String 
Dim sEndRange As String 

Dim lStartRow As Long 
Dim iStartCol As Integer 

Dim lEndRow As Long 
Dim iEndCol As Integer 

Dim lMatchRow As Variant 
Dim iCol As Integer 

    lMatchRow = 0 

    isMatchPresent = lMatchRow  

    If (InStr(1, searchRange.Address, ":") > 0) Then 

        sStartRange = Left(searchRange.Address, InStr(1, searchRange.Address, ":") - 1) 
        sEndRange = Mid(searchRange.Address, InStr(1, searchRange.Address, ":") + 1) 
          
    Else 
        sStartRange = searchRange.Address 
        sEndRange = searchRange.Address 
    End If 
     
    lStartRow = Range(sStartRange).Row 
    iStartCol = Range(sStartRange).Column 
     
    lEndRow = Range(sEndRange).Row 
    iEndCol = Range(sEndRange).Column 
     
    For iCol = iStartCol To iEndCol 
         
        lMatchRow = 0 

        On Error Resume Next 
         
            lMatchRow = WorksheetFunction.Match(lookfor, Range(Cells(lStartRow, iCol), Cells(lEndRow, iCol)), 0) 
         
        On Error GoTo 0 
             
        If lMatchRow <> 0 Then 
            isMatchPresent = lMatchRow + lStartRow - 1 
            Exit Function 
        End If 
         
    Next iCol 
     
    On Error Resume Next 
     
End Function

Macro to Find a info in more than one column

7 responses

Similar discussions

Subscribe To Our Newsletter!