Macro to Find a info in more than one column

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

7 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
AJ now can you explain your issue. Use the sample sheet to explain what you seek
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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?
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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", "")
Dear Rizvisa1,

Thank for the function and a query to execute it, but it seems to be not updating the status.
Follwoing are the steps I followed:
a. [ Alt + F11 ] to open [ Microsoft Visual Basic Editor ].
b. Insert a Module.
c. Pasted the copied function.
d. Quit from [ Microsoft Visual Basic Editor ] by pressing [ Alt + Q ]

e. In Worksheet copied the above [IF] query(Function).
But it is not showing any result. The result column is blank.

The sample file is uploaded on [ https://authentification.site/files/22317534/Daily_Running_Total_DashboardV2.xls ] password is [ bocuzudibeju ]

Kindly find some time to review to the sample file I have created and advise.

Thank you & Best Regards - AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
When I opened the book it showed result already
http://www.speedyshare.com/files/22317999/snap2.jpg
Dear Rizvisa1,

I have Excel 2000.
Could this be the reason that I am not able to see the results on my machine.
The interesting part is that it is not showing any error message. Please advise as this is such a tremendous solution I don't want to loose.

Thank you and Best Regards - AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Yeah seems like it. It works with office Xp but not with office 2007.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 
Wow, it worked now.
it is bit slow but the result is more important the speed in this case.

Your help and attention to the request is hightly appreciated.

Thank you and Best Regards - AJ
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Yeah I saw that too that this solution is a bit slot. You just have to upgrade to at least XP, then use original function. That one is faster. Other way would be to to do away with function and have a macro that do it.