Macro to Find a info in more than one column

Solved/Closed
AJ - May 7, 2010 at 04:34 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 10, 2010 at 08:52 AM
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
Related:

7 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 05:57 AM
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.
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 07:08 AM
AJ now can you explain your issue. Use the sample sheet to explain what you seek
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 08:27 AM
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?
0

Didn't find the answer you are looking for?

Ask a question
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 09:11 AM
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", "")
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 10:51 AM
When I opened the book it showed result already
http://www.speedyshare.com/files/22317999/snap2.jpg
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 02:42 PM
Yeah seems like it. It works with office Xp but not with office 2007.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 03:41 PM
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 
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 10, 2010 at 08:52 AM
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.
0