Find or Loop?

[Jameo]

Good day all,

I have a series of data consisting of a date column, and a value column. The values relate to actual flow rates of a gas at minute intervals, with the date column in the form of dd/mm/yyyy hh:mm.

I then have a seperate set of data, on a seperate sheet relating to the nominations (the amount of gas that acutally should have flowed). This data is again set up in two columns, a date column in the same format, and another value column.

This data is only given on the hour.

To align my data, I need to insert the nomination next to the actual flow. Obviously this is going to consist of the same nomination value against all actual flows in the same hour (60 readings).

Now normally I would just accomplish this with a coulple of loops. For each actual time and value I would cycle through the nominations and find the value for the matching time. However, this is not very efficient, and possibly be better using the find function?

Any input into the best way to procede would be appreicated.

Thanks

 

[p45cal]

Copy down the formula and copy | paste-special | Values?

Excel Workbook
	A	B	C	D	E	F
1	date/time	flow	nom.flow		date/time	hourly nominal
2	23/09/2011 08:00		1.1		23/09/2011 08:00	1.1
3	23/09/2011 08:01		1.1		23/09/2011 09:00	2.2
4	23/09/2011 08:02		1.1		23/09/2011 10:00	3.3
5	23/09/2011 08:03		1.1		23/09/2011 11:00	4.4
6	23/09/2011 08:04		1.1		23/09/2011 12:00	5.5
7	23/09/2011 08:51		1.1		23/09/2011 13:00	6.6
8	23/09/2011 08:52		1.1		23/09/2011 14:00	7.7
9	23/09/2011 08:53		1.1		23/09/2011 15:00	8.8
10	23/09/2011 08:54		1.1		23/09/2011 16:00	9.9
11	23/09/2011 08:55		1.1		23/09/2011 17:00	11
12	23/09/2011 08:56		1.1		23/09/2011 18:00	12.1
13	23/09/2011 08:57		1.1		23/09/2011 19:00	13.2
14	23/09/2011 08:58		1.1		23/09/2011 20:00	14.3
15	23/09/2011 08:59		1.1		23/09/2011 21:00	15.4
16	23/09/2011 09:00		2.2		23/09/2011 22:00	16.5
17	23/09/2011 09:01		2.2		23/09/2011 23:00	17.6
18	23/09/2011 09:02		2.2		24/09/2011 00:00	18.7
19	23/09/2011 09:57		2.2		24/09/2011 01:00	19.8
20	23/09/2011 09:58		2.2		24/09/2011 02:00	20.9
21	23/09/2011 09:59		2.2		24/09/2011 03:00	22
22	23/09/2011 10:00		3.3		24/09/2011 04:00	23.1
23	23/09/2011 10:01		3.3		24/09/2011 05:00	24.2
24	23/09/2011 10:02		3.3
25	23/09/2011 10:58		3.3
26	23/09/2011 10:59		3.3
27	23/09/2011 11:00		4.4
28	23/09/2011 11:01		4.4
29	23/09/2011 11:58		4.4
30	23/09/2011 11:59		4.4
31	23/09/2011 12:00		5.5
32	23/09/2011 12:01		5.5
33	24[/B]/09/2011 00:58		18.7
34	24/09/2011 00:59		18.7
35	24/09/2011 01:00		19.8
36	24/09/2011 01:01		19.8
37	24/09/2011 01:02		19.8
38	24/09/2011 01:03		19.8
39	24/09/2011 01:04		19.8
40	24/09/2011 01:05		19.8
41	24/09/2011 01:06		19.8
42	24/09/2011 01:07		19.8
43	24/09/2011 01:08		19.8
44	24/09/2011 01:09		19.8
Sheet28

 

[Jameo]

Thanks, but I have to do this for multiple data sets now and in the future. It really needs to be a VBA method.

I could easilly do this with a couple of loops. One to cycle through the actual values, and another to cycle through the nom values, and then copy and paste into the relevent position. However, if we get to the point where I have to do this for a years worth of data the inefficiency in the code would start to become an issue. I am really looking for a different methodology if possible.

Cheers for your help though

 

[p45cal]

With the same ranges as in my first response:

Sub blah()
With Range("C2:C44")
    .FormulaR1C1 = "=VLOOKUP(RC[-2],R1C5:R23C6,2)"
    '.FormulaR1C1 = "=VLOOKUP(RC[-2],NomFlow,2)"'instead, if you give you nominal flow table a name.
    .Value = .Value
End With
End Sub

 

[Jameo]

Not to sound ungrateful p45cal, but I am not happy with the idea of using vlookups purely because of the shear amount of data my workbook will eventually contain. The systems at work are not the best, and I can envisage the time when the calculations performed reach a point where they become a serious issue.

 

[Jameo]

For instance, the following code does the job that I want, just not ver efficeintly. Is there any other methods anyone could reccomend that would do the job better?

Option Explicit
Public Sub LOP()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim IntRow As Long, i As Long
Dim LRow1 As Long, LRow2 As Long
Dim CuTime As Long
Dim LOPCol As Long, LOPCol1 As Long
Set ws1 = Worksheets("IoG - Flow Total - 22nd Sep")
Set ws2 = Worksheets("IoG - LOP - 22nd Sep")
LRow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
LRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
IntRow = 1
LOPCol = 1
LOPCol1 = 4
    Do
    LOPCol = LOPCol + 1
    LOPCol1 = LOPCol1 + 1
    
        For i = 2 To LRow1
        IntRow = 1
        CuTime = Hour(ws1.Cells(i, 1).Value)
        
            Do
                IntRow = IntRow + 1
                If Hour(ws2.Cells(IntRow, 1).Value) = CuTime Then
                    ws2.Cells(IntRow, LOPCol).Copy Destination:=ws1.Cells(i, LOPCol1)
                End If
                
                
        
            Loop Until IntRow >= LRow2
        Next i
        
    Loop Until LOPCol = 3
End Sub

 

[p45cal]

Again, with the same ranges as my first response, try:

Sub blah()
With Range("A2:A44")
    xxx = .Value
    yyy = Range("$E$2:$F$23").Value    'your hourly nominal table
    Dim Result()
    ReDim Result(1 To UBound(xxx), 1 To 1)
    For i = 1 To UBound(xxx)
        lngxxx = Int(xxx(i, 1) + 0.0000000001)
        xxHour = Hour(xxx(i, 1))
        For j = 1 To UBound(yyy)
            lngyyy = Int(yyy(j, 1) + 0.0000000001)
            If lngyyy = lngxxx Then
                If Hour(yyy(j, 1)) = xxHour Then
                    Result(i, 1) = yyy(j, 2)
                    Exit For
                End If
            End If
        Next j
        If IsEmpty(Result(i, 1)) Then Stop
    Next i
    .Offset(, 2) = Result
End With    'Range("A2:A44")
End Sub

It tries to do much of the work in memory. I had a struggle with my dates; I try to compare both the integer parts of two dates and the Hour portion of the same two dates and if they're both the same, put the nominal flow value into the results array. This should be straightforward, however it turns out that when you try in vba to int() a date which is exactly midnight (cell E18 in my example above) you get the day before! On the worksheet you get the right date. Hence the ugly + 0.0000000001 parts of the code.

 

[p45cal]

I left a debug line in by mistake:
If IsEmpty(Result(i, 1)) Then Stop
needs to go.

 

[p45cal]

Just reviewing a few old threads - Jameo, was this any good?

 

[p45cal]

Just reviewing a few old threads - Jameo, was this any good?

Jameo, you posted some 10 new threads since I asked the above - would you do me the courtesy of giving me some feedback?