none
VBA for MS Excel - help using VLookup RRS feed

  • Question

  • I have one excel file with two sheets: sheet (1) with a data set containing a column of time stamps every minute (DD:MM:YY HH:MM:SS) and sheet (2) with corresponding data but with less frequent and different time points over the same time interval (DD-MMM-YY HH:MM AM/PM).

    Sheet (1)                                                          Sheet (2)

    Column (A)                   Column (B)                  Column (A)                     Column (B)

    01:01:11 05:00:00      33.335                         01-Jan-11 05:00 AM       0.9834

    01:01:11 05:01:00      35.432                         02-Jan-11 09:22 PM       0.8709

    01:01:11 05:02:00       98.768                         04-Jan-11 11:50 PM       0.0987

    01:01:11 05:03:00      24.980                         06-Jan-11 05:18 AM       0.9808

    .                                   .

    .                                   .

    .                                   .

    06:01:11 12:58:00       143.90

    06:01:11 12:59:00       345.90

    06:01:11 13:00:00       435.89

     

    I’m trying to write a macro to place the column (B) data from sheet (2) into column (C) of sheet (1) for the corresponding dates. 

    I’m guessing…

    1st: the format Sheet (1) Column (A) will need to be changed to match the format of Sheet (2) Column (A)

    2nd: a VLookup function will need to be used for matching the dates

    I’ve been looking for clues online and this link (http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/feaa76db-09bf-481b-a0d5-7866a97e1a04) was the closes I could find to helpful but I’m still struggling.

     


    • Edited by Mon27ster Wednesday, October 19, 2011 2:04 PM superscript edit
    Wednesday, October 19, 2011 2:02 PM

Answers

  • For anyone following this, Monica had date time values down to the second, but was only displaying to the minute. So the "Rounding function" I used was to lop off the seconds from the time - then it all worked.

     


    HTH, Bernie
    Thursday, October 20, 2011 4:44 PM

All replies

  • Try it like this, using code to enter a formula into column C of sheet 1 - I have assumed your data starts in row 2. The name for Sheet 1 is "Sheet 1" and likewise, for sheet 2 "Sheet 2"  If that is not the case, you need to change the names in the four places used.

    Sub GetDataFromSheet2()
        Dim lngRow As Long
        lngRow = Sheets("Sheet 1").Cells(Rows.Count, 1).End(xlUp).Row
        With Sheets("Sheet 1").Range("C2:C" & lngRow)
            .FormulaR1C1 = _
            "=IF(ISERROR(VLOOKUP(RC[-2],'Sheet 2'!C[-2]:C[-1],2,FALSE))," & _
                           """"",VLOOKUP(RC[-2],'Sheet 2'!C[-2]:C[-1],2,FALSE))"
            .Value = .Value
        End With
    End Sub

    PS - Format is ignored - just the value is used for VLOOKUP.


    HTH, Bernie

    Wednesday, October 19, 2011 2:47 PM
  • Very clever, thanks!  

     

    I see how this should work but it's not working.  

    I think the time format is messing it up still, so I tried it after making the time format the same for both sheets.  Still not working.

    In a off-to-side-cell I checked the first time match: "=A185='Sheet 2'!A2" which turned up false.  Weird, bc/ they are the same down to the second.

    Is there some way of using VLOOKUP to find the closes number?  Or is it no good with dates?

     

    Thanks again for your help!

    Monica

    Wednesday, October 19, 2011 3:46 PM
  • Monica,

    To troubleshoot, you could set both formats to general, and that will show a number like

    40544.2083333333

    (that's how Excel stores dates)

    The values need to be exactly equal. If they are not, then in another column, use a function like

    =ROUND(A2,5)

    and copy down, then copy the formulas and paste values over the original date times - to get the times to match.

    If you can't figure it out, send me the file at

    bdeitrick at alum dot mit dot edu


    HTH, Bernie
    Wednesday, October 19, 2011 4:14 PM
  • Hey Bernie,
    You were right about the date rounding!
    Now, it's working for the first value of Sheet '2' but it's not going beyond that first date.
    If you are willing to take a look, I will shoot you the file now.
    Many Thanks,
    Monica
    Wednesday, October 19, 2011 5:40 PM
  • For anyone following this, Monica had date time values down to the second, but was only displaying to the minute. So the "Rounding function" I used was to lop off the seconds from the time - then it all worked.

     


    HTH, Bernie
    Thursday, October 20, 2011 4:44 PM