Abstracting the data

S

Sunil Pinto

New Member
Joined
Jul 31, 2024
Messages
20
Office Version
  1. 2016
I have the data of the date and cars moved on a particular road.
I need the maximum care moved in a certain period out of data.
Example: According to the below data my from date is 6/10/2024 to 26/10/2024 and I need a maximum of cars moved in 2 consecutive days
Can anybody help me to design the formula?

1729627344312.png
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Seems to work using VBA.

EXCEL
BCDEFGHI
2DatesCarsFromToDays
310/1/2024510/6/202410/26/20242
410/2/20242
510/3/20245Answer17
610/4/20247From Date10/10/2024
710/5/20246To Date10/11/2024
810/6/20241
910/7/20242
1010/8/20245
1110/9/20247
1210/10/20248
1310/11/20249
1410/12/20245
1510/13/20243
1610/14/20242
1710/15/20241
1810/16/20244
1910/17/20245
2010/18/20246
2110/19/20247
2210/20/20244
2310/21/20243
2410/22/20242
2510/23/20241
2610/24/20246
2710/25/20248
2810/26/20244
2910/27/20243
3010/28/20245
3110/29/20247
3210/30/20249
Sheet10
Cell Formulas
RangeFormula
B4:B32B4=B3+1


VBA Code: 
Sub cars()
Dim AR() As Variant:        AR = Range("B3:C" & Range("C" & Rows.Count).End(xlUp).Row).Value2
Dim dFrom As Date:          dFrom = Range("G3").Value
Dim dTo As Date:            dTo = Range("H3").Value
Dim offset As Integer:      offset = Range("I3").Value - 1
Dim tmp As Integer:         tmp = 0
Dim hi As Integer:          hi = 0
Dim hii As Integer:         hii = -1

For i = 1 To UBound(AR) - offset
    If AR(i, 1) >= dFrom Then
        For j = 0 To offset
            If AR(i + j, 1) <= dTo Then
                tmp = tmp + AR(i + j, 2)
            End If
        Next j
        If tmp > hi Then
            hi = tmp
            hii = i
        End If
        tmp = 0
    End If
Next i

Range("I5").Value = hi
Range("I6").Value = AR(hii, 1)
Range("I7").Value = AR(hii + offset, 1)

End Sub
 
Upvote 0
lrobbo314 said:
Seems to work using VBA.

EXCEL
BCDEFGHI
2DatesCarsFromToDays
310/1/2024510/6/202410/26/20242
410/2/20242
510/3/20245Answer17
610/4/20247From Date10/10/2024
710/5/20246To Date10/11/2024
810/6/20241
910/7/20242
1010/8/20245
1110/9/20247
1210/10/20248
1310/11/20249
1410/12/20245
1510/13/20243
1610/14/20242
1710/15/20241
1810/16/20244
1910/17/20245
2010/18/20246
2110/19/20247
2210/20/20244
2310/21/20243
2410/22/20242
2510/23/20241
2610/24/20246
2710/25/20248
2810/26/20244
2910/27/20243
3010/28/20245
3110/29/20247
3210/30/20249
Sheet10
Cell Formulas
RangeFormula
B4:B32B4=B3+1


VBA Code: 
Sub cars()
Dim AR() As Variant:        AR = Range("B3:C" & Range("C" & Rows.Count).End(xlUp).Row).Value2
Dim dFrom As Date:          dFrom = Range("G3").Value
Dim dTo As Date:            dTo = Range("H3").Value
Dim offset As Integer:      offset = Range("I3").Value - 1
Dim tmp As Integer:         tmp = 0
Dim hi As Integer:          hi = 0
Dim hii As Integer:         hii = -1

For i = 1 To UBound(AR) - offset
    If AR(i, 1) >= dFrom Then
        For j = 0 To offset
            If AR(i + j, 1) <= dTo Then
                tmp = tmp + AR(i + j, 2)
            End If
        Next j
        If tmp > hi Then
            hi = tmp
            hii = i
        End If
        tmp = 0
    End If
Next i

Range("I5").Value = hi
Range("I6").Value = AR(hii, 1)
Range("I7").Value = AR(hii + offset, 1)

End Sub
Click to expand...
Sir,
Can you send me the Excel link, I pasted your formula in VBA, but how can I use it in Excel?
 
Upvote 0
Hello! Option using formula.
Book1.xlsm
BCDEFGHI
2DatesCarsFromToDays
301.10.2024506.10.202426.10.20242
402.10.20242
503.10.20245Answer17
604.10.20247From Date10.10.2024
705.10.20246To Date11.10.2024
806.10.20241
907.10.20242
1008.10.20245
1109.10.20247
1210.10.20248
1311.10.20249
1412.10.20245
1513.10.20243
1614.10.20242
1715.10.20241
1816.10.20244
1917.10.20245
2018.10.20246
2119.10.20247
2220.10.20244
2321.10.20243
2422.10.20242
2523.10.20241
2624.10.20246
2725.10.20248
2826.10.20244
2927.10.20243
3028.10.20245
3129.10.20247
3230.10.20249
Sheet11
Cell Formulas
RangeFormula
I5I5=SUMIFS(C3:C32,B3:B32,">="&I6,B3:B32,"<="&I7)
B4:B32B4=B3+1
 
Upvote 0
@Sergius I think the dates in I6 & I7 are meant to be worked out, as the two dates that return the highest value.
 
Upvote 0
Sunil,

I can't see what your range references are, so I'm not sure if everything is coded correctly. Let me know if your data starts in B2, like I assumed in the post I made.

If that is correct, after you have pasted the code, go back to your worksheet, hit Alt+F8, then run the macro.
 
Upvote 0
Here is a formula solution, but it relies on Office 365 formulas.

EXCEL
BCDEFGHIJKL
2DatesCarsFromToDays
310/1/2024510/6/202410/26/20243
410/2/20242
510/3/20245Answer24Answer24
610/4/20247From Date10/9/2024From Date10/9/2024
710/5/20246To Date10/11/2024To Date10/11/2024
810/6/20241
910/7/20242
1010/8/20245
1110/9/20247
1210/10/20248
1310/11/20249
1410/12/20245
1510/13/20243
1610/14/20242
1710/15/20241
1810/16/20244
1910/17/20245
2010/18/20246
2110/19/20247
2210/20/20244
2310/21/20243
2410/22/20242
2510/23/20241
2610/24/20246
2710/25/20248
2810/26/20244
2910/27/20243
3010/28/20245
3110/29/20247
3210/30/20249
Sheet10


Excel Formula: 
=LET(
    f,FILTER(B3:C32,(B3:B32>=G3)*(B3:B32<=H3)),
    v,INDEX(f,,2),
    os,I3,
    br,BYROW(INDEX(v,MAKEARRAY(ROWS(v)-os,os,LAMBDA(r,c,r+c-1))),LAMBDA(b,SUM(b))),
    hi,MAX(br),
    ld,INDEX(INDEX(f,,1),XMATCH(hi,br)),
    hd,ld+os-1,
    VSTACK(hi,ld,hd)
)
 
Upvote 0
The data is in col E
expirement.xlsx
EFGHIJ
1
2
3DatesCarsFrom DateTo DateDays
401/10/2024506/10/202426/10/20242
502/10/20242
603/10/20245Answer17
704/10/20247From Date10/10/2024
805/10/20246To date11/10/2024
906/10/20241
1007/10/20242
1108/10/20245
1209/10/20247
1310/10/20248
1411/10/20249
1512/10/20245
1613/10/20243
1714/10/20242
1815/10/20241
1916/10/20244
2017/10/20245
2118/10/20246
2219/10/20247
2320/10/20244
2421/10/20243
2522/10/20242
2623/10/20241
2724/10/20246
2825/10/20248
2926/10/20244
3027/10/20243
3128/10/20245
3229/10/20247
3330/10/20249
Sheet1
 
Upvote 0
Here is updated code that should work on those ranges.

VBA Code: 
Sub cars()
Dim AR() As Variant:        AR = Range("E4:F" & Range("F" & Rows.Count).End(xlUp).Row).Value2
Dim dFrom As Date:          dFrom = Range("H4").Value
Dim dTo As Date:            dTo = Range("I4").Value
Dim offset As Integer:      offset = Range("J4").Value - 1
Dim tmp As Integer:         tmp = 0
Dim hi As Integer:          hi = 0
Dim hii As Integer:         hii = -1

For i = 1 To UBound(AR) - offset
    If AR(i, 1) >= dFrom Then
        For j = 0 To offset
            If AR(i + j, 1) <= dTo Then
                tmp = tmp + AR(i + j, 2)
            End If
        Next j
        If tmp > hi Then
            hi = tmp
            hii = i
        End If
        tmp = 0
    End If
Next i

Range("J6").Value = hi
Range("J7").Value = AR(hii, 1)
Range("J8").Value = AR(hii + offset, 1)

End Sub
 
Upvote 0
Also,

here is a fairly easy way using pre-365 formulas. Just using 1 helper column.

EXCEL
EFGHIJKLM
3DatesCarsHelperFromToDays
410/1/20245010/6/202410/26/20242
510/2/202420
610/3/202450Answer17
710/4/202470From Date10/10/2024
810/5/202460To Date10/11/2024
910/6/202413
1010/7/202427
1110/8/2024512
1210/9/2024715
1310/10/2024817
1410/11/2024914
1510/12/202458
1610/13/202435
1710/14/202423
1810/15/202415
1910/16/202449
2010/17/2024511
2110/18/2024613
2210/19/2024711
2310/20/202447
2410/21/202435
2510/22/202423
2610/23/202417
2710/24/2024614
2810/25/2024812
2910/26/202447
3010/27/202430
3110/28/202450
3210/29/202470
3310/30/202490
Sheet10
Cell Formulas
RangeFormula
M6M6=MAX(G4:G33)
M7M7=INDEX(E4:E33,MATCH(M6,G4:G33,0))
M8M8=M7+L4-1
E5:E33E5=E4+1
G4:G33G4=SUM(F4:OFFSET(F4,$L$4-1,0))*(E4>=$J$4)*(E4<=$K$4)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question Question
Plot x axis with dates with non standard interval
Replies
1
Views
94
saboh12617
S
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
118
Abdo
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
510
Peter_SSs
Peter_SSs
M
  • Solved
Count Unique Values Based on Unique (sort of) Criteria
Replies
4
Views
226
Fluff
Fluff
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
127
RobP
R

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top