• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Joining matching criteria & if possible vba code

P

paradise

Member
Hi,

The array formula which is
{=IFERROR(INDEX('List T'!$B$4:$B$13327,MATCH('List S'!B2&'List S'!M2&'List S'!O2,'List T'!$I$4:$I$13327&'List T'!$E$4:$E$13327&'List T'!$H$4:$H$13327,0)),"NOT AVAILABLE")}

works fine.However one of the data which I am referencing in 'List T'!$I$4:$I$13327 has the formula throughout I column is

='List T'!G4&"/"&MID('List T'!D4,4,255)

What I want is that I want to use ='List T'!G4&"/"&MID('List T'!D4,4,255) in above array formula instead of using 'List T'!$I$4:$I$13327.

Can anyone suggest me correct formula.And if possible vba code.I would very much appreciate.
 
Pls find enclosed.I have deleted other remaining data as it was huge.helper column needed to be omitted which I have said in above.
 

Attachments

  • Match.xlsx
    13.8 KB · Views: 1
Hi:

You should have posted this in VBA Macro section as Marc mentioned in his post. But I already had worked on this code, find the attached. I have not tested it extensively anyhow its working for whatever data set you had posted in your OP.

Click on the button "Run" it will give you results is column Q.

Thanks
 

Attachments

  • Match (1).xlsm
    22.9 KB · Views: 0
Heaps of Thanx for the reply and providing me code.

In reality I have more than 13000 rows filled up with data.Does this work on that ? Kindly let me know.

Can add in your code regarding highlighting of the result where "Not Available" appears in column Q.

Secondly, can u explain me the below portion of your code.

Code: 
For j = 2 To i
  For m = 4 To l
  If Sheet1.Range("B" & j) = Sheet2.Range("G" & m) & "/" & Mid(Sheet2.Range("D" & m), 4, 255) And Sheet1.Range("M" & j) = Sheet2.Range("E" & m) And Sheet1.Range("O" & j) = Sheet2.Range("H" & m) Then
  Sheet1.Range("Q" & j) = Sheet2.Range("B" & m)
  ElseIf Sheet1.Range("Q" & j) = "" Then

I will be much more thankful if u help me learning those codes and a step ahead to go for me.
 
Hi:

Find the attached with changes. Yes, the code can accommodate "n" no:eek:f rows. As for the portion of the code you had in your post. It simply checks the conditions you had given in the formula using if statements. In short, it does the same checks your array formula had done for you.

Thanks
 

Attachments

  • Match (1).xlsm
    24 KB · Views: 0
Hi:

You do not have to remove data or do anything , Just press the button the code will do everything for you.

Thanks
 
Ok, 2 To i' and '4 To l' is for the "for loop". 2 means the starting row of your data in tab List S , "i" is the count of total rows in your tab List S (in this case I guess its 15), "i" is also dynamic so whenever the no:eek:f rows in your data changes the counter "i" will increase or decrease depending on the change. As I mentioned in my earlier post since the"i" is flexible it can accommodate "n" no:eek:f rows. The same goes for 4 to l which is for tab List T. Hope the explanation make sense to you.

Thanks
 
I think there is problem when I am using your code in the large data and it gets hanged and not displaying the result.

In above your code was fine but in large data it is showing problem.

How can I send u that private file.Since the file is confidential type.
 
Hi:

I am currently working on it , will let you know once done. I was busy in office this morning.

Thanks
 
Hi:

Here is the code , Right click on List S tab Click on view code and paste it there.

It is taking around 3 minutes to run the macro . I have taken out the "Not Available" flag since it add more time to the macro to execute , I guess from blank cells you can identify the not available data. I have used arrays for this , all the checking will be done in the memory of the system and will paste the values in the column Q only if it meets the criteria. I will send you a copy of the workbook from my personal email.

Code: 
Sub Compare()
Application.ScreenUpdating = False

Dim i As Long, j As Long, m As Long
Dim MyArray As Variant, MyArray1 As Variant
Dim StartTime As Double, Timelapsed As Double

MyArray = Range("B2:Q" & Cells(Rows.Count, "B").End(xlUp).Row).Value
MyArray1 = Sheet2.Range("B4:H" & Sheet2.Cells(Rows.Count, "B").End(xlUp).Row).Value
StartTime = Timer
i = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Sheet1.Range("Q2:Q" & i).ClearContents

For j = 1 To UBound(MyArray, 1)
    For m = 1 To UBound(MyArray1, 1)
        If MyArray(j, 1) = MyArray1(m, 6) & "/" & Mid(MyArray1(m, 3), 4, 255) And MyArray(j, 14) = MyArray1(m, 7) And MyArray(j, 12) = MyArray1(m, 4) Then
            Cells(j + 1, 17) = MyArray1(m, 1)
'        ElseIf Cells(j + 1, 17) = "" Then
'            Cells(j + 1, 17) = "Not Available"
        End If
    Next
Next
Timelapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & Timelapsed & " seconds", vbInformation
Application.ScreenUpdating = True
End Sub

Thanks
 
You must log in or register to reply here.
Back
Top