• 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.

compare two range with different format

D

Dalia

Member
I have two ranges. One with general number starts from 1 to 10 and another with date format 1 sep 2016, 2 sep 2016.. 10 sep 2016. Now if the "dd" in the date matches with the general number I need to paste value to the same row. for eg: 1 sept matches with 1, 2 sept matches with 2 and so on, so I need to paste the data in that row. kindly help
 
Can you please post a sample file with an example of the answer
 
hi,

Sorry I cannot uploa an excel file but below is screenshot. Column j in date format and column A in general format. Need to copy highlighted cell in col B
upload_2016-10-6_14-53-14.png
 
B1: =INDEX($K$4:$K$17,MATCH(DATE(YEAR(J$4),MONTH(J$4),A1),$J$4:$J$17,0))
Copy down
 
Hi
Thank you :)
I need a vba code on that and this is not the only column to paste there are multiple columns. Kindly help
Regards
 
I want to add few more points
the sheet is a protected worksheet. so am unable to put the formula in te cell it is coming as N/A. is there any other way to write the vba code
 
May be
Code: 
Sub Test()
    Dim Arr As Variant, ArrOut As Variant
    Dim I As Long, INDX As Long

    Arr = Range("J4").CurrentRegion.value
   
    For I = LBound(Arr, 1) To UBound(Arr, 1)
        If IsDate(Arr(I, 1)) Then Arr(I, 1) = Day(Arr(I, 1))
    Next I

    Columns(2).ClearContents
    ArrOut = Range("A1").CurrentRegion.Resize(, 2).value
   
    For I = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If IsError(Application.Match(Cells(I, 1), Application.Index(Arr, 0, 1), 0)) Then
            ArrOut(I, 2) = ""
        Else
            INDX = Application.Match(Cells(I, 1), Application.Index(Arr, 0, 1), 0)
            ArrOut(I, 2) = Arr(INDX, 2)
        End If
    Next I
   
    Range("A1").Resize(UBound(ArrOut, 1), UBound(ArrOut, 2)).value = ArrOut
End Sub
 
Hi Hui,
The formula is working now but I want to write a VBA code on that. How to do that. The input and output are in two different workbook. And table array of match is a dynamic range. It will move down in a single column as per requiremwnt
 
Hi Hui,

Yes but I cannot figure out where am going wrong in that. but your formula works perfectly. Can you provide me the vba code. the input and output are in two different workbook and the index table array is a dynamic range
 
Hi, I have written a formula if they are on the same sheet
With Worksheets("sht2")
.Range("c10:c40").Formula = "=INDEX($Y$10:$Y " & lstrw & " ,MATCH(DATE(YEAR(W$10),MONTH(W$10),A17),$W$10:$W$15,0))"
End With
getting application defined error. please help
 
May be if your variable is sht2 you would use
Code: 
With sht2
    .Range("c10:c40").Formula = "=INDEX($Y$10:$Y " & lstrw & " ,MATCH(DATE(YEAR(W$10),MONTH(W$10),A17),$W$10:$W$15,0))"
End With
 
still not happening. Same error (run time error 1004) below is the code

Sub function_indexmatch()
Dim lstrw As Long
Dim sht As Worksheet

lstrw = Cells(Rows.Count, "W").End(xlUp).Row
Set sht = Worksheets("sheet2")
With sht
.Range("c10:c40").Formula = "=INDEX($Y$10:$Y " & lstrw & " ,MATCH(DATE(YEAR(W$10),MONTH(W$10),A17),$W$10:$W$15,0))"
End With

End Sub
 
Dahlia

Without a sample file it makes solving this very difficult
 
Hi ,

The problem is an additional space character in the line :

.Range("c10:c40").Formula = "=INDEX($Y$10:$Y " & lstrw & " ,MATCH(DATE(YEAR(W$10),MONTH(W$10),A17),$W$10:$W$15,0))"

I have highlighted the Y and the " to show the unwanted space character in between. Remove it.

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