Finding biggest observation conditioned on several factors

H

homie

Board Regular
Joined
Nov 30, 2014
Messages
66
Hi,

I need to make a formula to return the largest number of Deposits of NOK for any given day in the previous 30 days. The deposits are timestamped by the minute. So I need to add the number of deposits per day for each day of the previous 30 days and then find the largest number of deposits for any of those days.

This formula/solution needs to be in one cell. I will copy it downward and the "present day" will change, so the previous 30 days will change as I copy it downward. The table below is where I get the information, but I will present the relevant data in another sheet entirely.

How do I go about solving this?

2nd related problem: I also need to find the largest Amount Deposited of NOK for any given day in the previous 30 days.

The dataset where I will be looking, looks like this:

[TABLE="width: 972"]
<tbody>[TR]
[TD]Timestamp recognized by Excel[/TD]
[TD]Timestamp recognized by Excel, rounded[/TD]
[TD]Remove USD,EUR,NOK or XBT, ETH, LTC from string[/TD]
[TD]Amount American number as text[/TD]
[TD]Currency[/TD]
[TD]Amount European number[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 00:09[/TD]
[TD="align: right"]16.09.2015 00:09[/TD]
[TD]9/16/2015 0:09,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 00:15[/TD]
[TD="align: right"]16.09.2015 00:15[/TD]
[TD]9/16/2015 0:15,100[/TD]
[TD]100[/TD]
[TD]USD[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 05:54[/TD]
[TD="align: right"]16.09.2015 05:54[/TD]
[TD]9/16/2015 5:54,0.30015[/TD]
[TD]0.30015[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,30015[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 14:34[/TD]
[TD="align: right"]16.09.2015 14:34[/TD]
[TD]9/16/2015 14:34,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 17:51[/TD]
[TD="align: right"]16.09.2015 17:51[/TD]
[TD]9/16/2015 17:51,4800[/TD]
[TD]4800[/TD]
[TD]NOK[/TD]
[TD="align: right"]4800[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 18:19[/TD]
[TD="align: right"]16.09.2015 18:19[/TD]
[TD]9/16/2015 18:19,1000[/TD]
[TD]1000[/TD]
[TD]NOK[/TD]
[TD="align: right"]1000[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 20:19[/TD]
[TD="align: right"]16.09.2015 20:19[/TD]
[TD]9/16/2015 20:19,0.2699[/TD]
[TD]0.2699[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,2699[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015 21:04[/TD]
[TD="align: right"]16.09.2015 21:04[/TD]
[TD]9/16/2015 21:04,0.756[/TD]
[TD]0.756[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,756[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 05:30[/TD]
[TD="align: right"]17.09.2015 05:30[/TD]
[TD]9/17/2015 5:30,2015[/TD]
[TD]2015[/TD]
[TD]NOK[/TD]
[TD="align: right"]2015[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 06:50[/TD]
[TD="align: right"]17.09.2015 06:50[/TD]
[TD]9/17/2015 6:50,200[/TD]
[TD]200[/TD]
[TD]NOK[/TD]
[TD="align: right"]200[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 07:40[/TD]
[TD="align: right"]17.09.2015 07:40[/TD]
[TD]9/17/2015 7:40,900[/TD]
[TD]900[/TD]
[TD]NOK[/TD]
[TD="align: right"]900[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 08:49[/TD]
[TD="align: right"]17.09.2015 08:49[/TD]
[TD]9/17/2015 8:49,0.52[/TD]
[TD]0.52[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,52[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 08:53[/TD]
[TD="align: right"]17.09.2015 08:53[/TD]
[TD]9/17/2015 8:53,0.37501792[/TD]
[TD]0.37501792[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,37501792[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:07[/TD]
[TD="align: right"]17.09.2015 11:07[/TD]
[TD]9/17/2015 11:07,0.02707997[/TD]
[TD]0.02707997[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,02707997[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:26[/TD]
[TD="align: right"]17.09.2015 11:26[/TD]
[TD]9/17/2015 11:26,0.80150239[/TD]
[TD]0.80150239[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,80150239[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD]9/17/2015 11:28,0.0724[/TD]
[TD]0.0724[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,0724[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD="align: right"]17.09.2015 11:28[/TD]
[TD]9/17/2015 11:28,0.5[/TD]
[TD]0.5[/TD]
[TD]LTC[/TD]
[TD="align: right"]0,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 12:08[/TD]
[TD="align: right"]17.09.2015 12:08[/TD]
[TD]9/17/2015 12:08,0.1051[/TD]
[TD]0.1051[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,1051[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 12:27[/TD]
[TD="align: right"]17.09.2015 12:27[/TD]
[TD]9/17/2015 12:27,0.4[/TD]
[TD]0.4[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,4[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 16:30[/TD]
[TD="align: right"]17.09.2015 16:30[/TD]
[TD]9/17/2015 16:30,2500[/TD]
[TD]2500[/TD]
[TD]NOK[/TD]
[TD="align: right"]2500[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 16:33[/TD]
[TD="align: right"]17.09.2015 16:33[/TD]
[TD]9/17/2015 16:33,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:40[/TD]
[TD="align: right"]17.09.2015 17:40[/TD]
[TD]9/17/2015 17:40,1.5[/TD]
[TD]1.5[/TD]
[TD]XBT[/TD]
[TD="align: right"]1,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:55[/TD]
[TD="align: right"]17.09.2015 17:55[/TD]
[TD]9/17/2015 17:55,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015 17:57[/TD]
[TD="align: right"]17.09.2015 17:57[/TD]
[TD]9/17/2015 17:57,500[/TD]
[TD]500[/TD]
[TD]NOK[/TD]
[TD="align: right"]500[/TD]
[TD]Deposit[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Sort by date Sort by votes
Have to think how to create a macro...
Questions
How many rows of data do you have?
Are the dates (days) always in ascending order?

M.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
homie said:
P.S. In my Array formulas I have references for 99 000 rows, e.g. the equivalent of "B$2:B$25" in your formula above I have "B$8:B$99000". Does that slow down Excel? I have 10 000 rows in my dataset so far, but it might be expanded to 20 000 when I get more data, I just wrote 99 000 to be safe. But does this slow down Excel even if there is no data in most of these rows?
Click to expand...

With 99K rows the array formulas will almost certainly have a horrible performance. I think it is not a viable solution.
You did not answer the question if the dates (days) are always in ascending order. I'm trying to create a macro, that should be quick, but i'm assuming the dates are sorted. Could you confirm?

M.
 
Upvote 0
Marcelo Branco said:
With 99K rows the array formulas will almost certainly have a horrible performance. I think it is not a viable solution.
You did not answer the question if the dates (days) are always in ascending order. I'm trying to create a macro, that should be quick, but i'm assuming the dates are sorted. Could you confirm?

M.
Click to expand...
Hi,

I had to get to bed, time difference you know, hehe. Yeah, I've sorted the dates in ascending order. So far there are 10 300 rows, but it might be as much as 25 000 in the end.
 
Upvote 0
Marcelo Branco said:
With 99K rows the array formulas will almost certainly have a horrible performance. I think it is not a viable solution.
You did not answer the question if the dates (days) are always in ascending order. I'm trying to create a macro, that should be quick, but i'm assuming the dates are sorted. Could you confirm?

M.
Click to expand...

It's only 11 000 rows of data, but still way too slow. With 8 array formulas per row (derived from the two you gave), 120 rows of those, 11 000 rows of input data plus another 5000 or so in a reference sheet (which I look up for Bitcoin prices), plus plenty more formulas it is just too slow. When I press "Calculate Now" it takes about 30 minutes for Excel to calculate.

How do I solve this using Macros?
 
Upvote 0
I don't why you need 8 formulas per row. According what you've told us till now, i created the macro below thinking in 4 results per row: Largest Num per Day; Day Largest Num; Largest Deposit any day; and Day Largest Deposit

Data in Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B
​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Timestamp​
[/TD]
[TD]
Day​
[/TD]
[TD]
Currency​
[/TD]
[TD]
Amount European number​
[/TD]
[TD]
Action​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
16/09/2015 00:09​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
16/09/2015 00:15​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
USD​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
16/09/2015 05:54​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,30015​
[/TD]
[TD]
Withdraw
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
16/09/2015 14:34​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
16/09/2015 17:51​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
4800​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
16/09/2015 18:19​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
1000​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
16/09/2015 20:19​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,2699​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
16/09/2015 21:04​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,756​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
17/09/2015 05:30​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2015​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
17/09/2015 06:50​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
200​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
17/09/2015 07:40​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
900​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
17/09/2015 08:49​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,52​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
17/09/2015 08:53​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,37501792​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
17/09/2015 11:07​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,02707997​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
17/09/2015 11:26​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,80150239​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
17/09/2015 11:28​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,0724​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
17/09/2015 11:28​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
LTC​
[/TD]
[TD]
0,5​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
17/09/2015 12:08​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,1051​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
17/09/2015 12:27​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,4​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
17/09/2015 16:30​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2500​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
17/09/2015 16:33​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
17/09/2015 17:40​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
1,5​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
17/09/2015 17:55​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
17/09/2015 17:57​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
500​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
</tbody>[/TABLE]


Sub Main and Functions
Code: 
Option Explicit

Sub Main()
    Dim lastRow1 As Long, lastRow2 As Long, lDataFirst As Long, lDataLast As Long
    Dim shData As String, rData As Range, i As Long
    
    shData = "Sheet1"
    With Sheets(shData)
        lastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rData = .Range("B2:E" & lastRow1)
    End With
        
    With Sheets("Sheet2")
        lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastRow2
            lDataFirst = GetFirst(rData.Columns(1), .Range("A" & i) - .Range("B" & i))
            lDataLast = GetLast(rData.Columns(1), .Range("A" & i))
            If lDataFirst > lDataLast Then
                .Range("E" & i).Resize(, 4) = Array(0, "", 0, "")
            Else
                .Range("E" & i).Resize(, 4) = GetResults(rData, lDataFirst, lDataLast, _
                                .Range("C" & i).Value, .Range("D" & i).Value)
            End If
        Next i
    End With
End Sub

Function GetFirst(r As Range, lDay As Date)
    Dim rFound As Range, lFirst As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(r.Rows.Count), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    
    If rFound Is Nothing Then
        lFirst = Application.Match(CLng(lDay), r)
        If IsError(lFirst) Then
            GetFirst = 1
        Else
            GetFirst = lFirst + 1
        End If
    Else
        GetFirst = rFound.Row - r.Row + 1
    End If
End Function

Function GetLast(r As Range, lDay As Date)
    Dim rFound As Range, lLast As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(1), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
    
    If rFound Is Nothing Then
        lLast = Application.Match(CLng(lDay), r)
        If IsError(lLast) Then
            GetLast = 0
        Else
            GetLast = lLast
        End If
    Else
        GetLast = rFound.Row - r.Row + 1
    End If
End Function

Function GetResults(r As Range, lFirst As Long, lLast As Long, Crit1 As String, Crit2 As String)
    Dim vData As Variant, dic As Object, vKey As Variant
    Dim i As Long, lMaxCount As Long, dbMaxAmount As Double
    Dim DayMaxCount As Variant, DayMaxAmount As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    vData = Range(r.Cells(lFirst, 1), r.Cells(lLast, 4))
    For i = LBound(vData, 1) To UBound(vData, 1)
        If UCase(vData(i, 2)) = UCase(Crit1) And UCase(vData(i, 4)) = UCase(Crit2) Then
            If dic.exists(vData(i, 1)) Then
                dic(vData(i, 1)) = Array(dic(vData(i, 1))(0) + 1, dic(vData(i, 1))(1) + vData(i, 3))
            Else
                dic.Add vData(i, 1), Array(1, vData(i, 3))
            End If
        End If
    Next i
    
    For Each vKey In dic.keys
        If dic(vKey)(0) > lMaxCount Then
            lMaxCount = dic(vKey)(0)
            DayMaxCount = vKey
        End If
        If dic(vKey)(1) > dbMaxAmount Then
            dbMaxAmount = dic(vKey)(1)
            DayMaxAmount = vKey
        End If
    Next vKey
    If lMaxCount = 0 Then DayMaxCount = ""
    If dbMaxAmount = 0 Then DayMaxAmount = ""
    GetResults = Array(lMaxCount, DayMaxCount, dbMaxAmount, DayMaxAmount)
End Function

Sheet2
Criteria light blue area
Results light gray area

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Ref Date​
[/TD]
[TD]
Days Before​
[/TD]
[TD]
Currency​
[/TD]
[TD]
Action​
[/TD]
[TD]
Largest Num per Day​
[/TD]
[TD]
Day Largest Num​
[/TD]
[TD]
Largest Deposit any day​
[/TD]
[TD]
Day Largest Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
6115​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
16/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
4​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
6000​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
USD​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
100​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
USD​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
10​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
3,85110028​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2017​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2014​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
</tbody>[/TABLE]


Possibly (i hope ;)) the code may be adpated for more results.

M.
 
Upvote 0
Marcelo Branco said:
I don't why you need 8 formulas per row. According what you've told us till now, i created the macro below thinking in 4 results per row: Largest Num per Day; Day Largest Num; Largest Deposit any day; and Day Largest Deposit

Data in Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B
​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Timestamp​
[/TD]
[TD]
Day​
[/TD]
[TD]
Currency​
[/TD]
[TD]
Amount European number​
[/TD]
[TD]
Action​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
16/09/2015 00:09​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
16/09/2015 00:15​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
USD​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
16/09/2015 05:54​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,30015​
[/TD]
[TD]
Withdraw
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
16/09/2015 14:34​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
100​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
16/09/2015 17:51​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
4800​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
16/09/2015 18:19​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
1000​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
16/09/2015 20:19​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,2699​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
16/09/2015 21:04​
[/TD]
[TD]
16/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,756​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
17/09/2015 05:30​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2015​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
17/09/2015 06:50​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
200​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
17/09/2015 07:40​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
900​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
17/09/2015 08:49​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,52​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
17/09/2015 08:53​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,37501792​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
17/09/2015 11:07​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,02707997​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
17/09/2015 11:26​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,80150239​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
17/09/2015 11:28​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,0724​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
17/09/2015 11:28​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
LTC​
[/TD]
[TD]
0,5​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
17/09/2015 12:08​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,1051​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
17/09/2015 12:27​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,4​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
17/09/2015 16:30​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
2500​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
17/09/2015 16:33​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
17/09/2015 17:40​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
1,5​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
17/09/2015 17:55​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
XBT​
[/TD]
[TD]
0,025​
[/TD]
[TD]
Withdraw​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
17/09/2015 17:57​
[/TD]
[TD]
17/09/2015​
[/TD]
[TD]
NOK​
[/TD]
[TD]
500​
[/TD]
[TD]
Deposit​
[/TD]
[/TR]
</tbody>[/TABLE]


Sub Main and Functions
Code: 
Option Explicit

Sub Main()
    Dim lastRow1 As Long, lastRow2 As Long, lDataFirst As Long, lDataLast As Long
    Dim shData As String, rData As Range, i As Long
    
    shData = "Sheet1"
    With Sheets(shData)
        lastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rData = .Range("B2:E" & lastRow1)
    End With
        
    With Sheets("Sheet2")
        lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastRow2
            lDataFirst = GetFirst(rData.Columns(1), .Range("A" & i) - .Range("B" & i))
            lDataLast = GetLast(rData.Columns(1), .Range("A" & i))
            If lDataFirst > lDataLast Then
                .Range("E" & i).Resize(, 4) = Array(0, "", 0, "")
            Else
                .Range("E" & i).Resize(, 4) = GetResults(rData, lDataFirst, lDataLast, _
                                .Range("C" & i).Value, .Range("D" & i).Value)
            End If
        Next i
    End With
End Sub

Function GetFirst(r As Range, lDay As Date)
    Dim rFound As Range, lFirst As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(r.Rows.Count), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
    
    If rFound Is Nothing Then
        lFirst = Application.Match(CLng(lDay), r)
        If IsError(lFirst) Then
            GetFirst = 1
        Else
            GetFirst = lFirst + 1
        End If
    Else
        GetFirst = rFound.Row - r.Row + 1
    End If
End Function

Function GetLast(r As Range, lDay As Date)
    Dim rFound As Range, lLast As Variant
    
    Set rFound = r.Find(lDay, After:=r.Cells(1), _
        Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
    
    If rFound Is Nothing Then
        lLast = Application.Match(CLng(lDay), r)
        If IsError(lLast) Then
            GetLast = 0
        Else
            GetLast = lLast
        End If
    Else
        GetLast = rFound.Row - r.Row + 1
    End If
End Function

Function GetResults(r As Range, lFirst As Long, lLast As Long, Crit1 As String, Crit2 As String)
    Dim vData As Variant, dic As Object, vKey As Variant
    Dim i As Long, lMaxCount As Long, dbMaxAmount As Double
    Dim DayMaxCount As Variant, DayMaxAmount As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    vData = Range(r.Cells(lFirst, 1), r.Cells(lLast, 4))
    For i = LBound(vData, 1) To UBound(vData, 1)
        If UCase(vData(i, 2)) = UCase(Crit1) And UCase(vData(i, 4)) = UCase(Crit2) Then
            If dic.exists(vData(i, 1)) Then
                dic(vData(i, 1)) = Array(dic(vData(i, 1))(0) + 1, dic(vData(i, 1))(1) + vData(i, 3))
            Else
                dic.Add vData(i, 1), Array(1, vData(i, 3))
            End If
        End If
    Next i
    
    For Each vKey In dic.keys
        If dic(vKey)(0) > lMaxCount Then
            lMaxCount = dic(vKey)(0)
            DayMaxCount = vKey
        End If
        If dic(vKey)(1) > dbMaxAmount Then
            dbMaxAmount = dic(vKey)(1)
            DayMaxAmount = vKey
        End If
    Next vKey
    If lMaxCount = 0 Then DayMaxCount = ""
    If dbMaxAmount = 0 Then DayMaxAmount = ""
    GetResults = Array(lMaxCount, DayMaxCount, dbMaxAmount, DayMaxAmount)
End Function

Sheet2
Criteria light blue area
Results light gray area

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Ref Date​
[/TD]
[TD]
Days Before​
[/TD]
[TD]
Currency​
[/TD]
[TD]
Action​
[/TD]
[TD]
Largest Num per Day​
[/TD]
[TD]
Day Largest Num​
[/TD]
[TD]
Largest Deposit any day​
[/TD]
[TD]
Day Largest Deposit​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
6115​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
16/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
4​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
6000​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
USD​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
100​
[/TD]
[TD="bgcolor: #D9D9D9"]
16/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
USD​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2015​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
XBT​
[/TD]
[TD="bgcolor: #DCE6F1"]
Withdraw​
[/TD]
[TD="bgcolor: #D9D9D9"]
10​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[TD="bgcolor: #D9D9D9"]
3,85110028​
[/TD]
[TD="bgcolor: #D9D9D9"]
17/09/2015​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2017​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD="bgcolor: #DCE6F1"]
17/09/2014​
[/TD]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD="bgcolor: #DCE6F1"]
NOK​
[/TD]
[TD="bgcolor: #DCE6F1"]
Deposit​
[/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
</tbody>[/TABLE]


Possibly (i hope ;)) the code may be adpated for more results.

M.
Click to expand...

Thanks!

Well, it's actually
-Largest number of deposits for any given day (in the past 30)
-Smallest number of deposits for any given day (in the past 30)
-Largest amount deposited for any given day, calculated by totalling all deposits that day (in the past 30)
-Smallest (or 2nd smallest) amount deposited for any given day, calculated by totalling all deposits that day (in the past 30)

That's the 4 results per row, what we covered in the thread so far, and what I've used in the model. That is for currency NOK, then I use the same formulas for currency BTC, for a total of 8 results per row. Does this Macro do the 4 mentioned above?

Thanks so much! :) This looks like Visual Basic, which I haven't done since High School, but it will be interesting to work out :D
 
Upvote 0
You are very welcome,

If i understood correctly, the requirements are:
-For currencies NOK and BTC:
1.LDA = Largest number of deposits for any given day
2.LAA = Largest amount deposited for any given day, calculated by totalling all deposits that day
3.SDA = Smallest number of deposits (greater than zero) for any given day
4.SAA = Smallest amount (greater than zero) deposited for any given day, calculated by totaling all deposits that day

- Show the days where such values occur

- All in the past past 30 days (you can define a different number of days in column B, if you want)

Goals 1 and 2 already achieved (macro above). Now, I'll try numbers 3 and 4

M.
 
Last edited:
Upvote 0
Could you provide a data sample that also contains deposits/withdrawals for BTC?
The data sample you've already provided contains no values for such currency.

M.
 
Last edited:
Upvote 0
Marcelo Branco said:
Could you provide a data sample that also contains deposits/withdrawals for BTC?
The data sample you've already provided contains no values for such currency.

M.
Click to expand...


Here we go. I don't know how to paste the data in a neat table like you did earlier. Here it is though: (XBT is the denotion of BTC)
Note: We can exclude the (smaller than zero) requirements for your points 3 and 4 above. This is because if it makes the Model faster and simpler it is worth it. Also, with the newer data zero deposits and zero withdrawels seems not to be a thing anymore. Likely due to the increased customer base (more deposits/withdrawels).


[TABLE="width: 683"]
<tbody>[TR]
[TD]Date only[/TD]
[TD]Remove USD,EUR,NOK or XBT, ETH, LTC from string[/TD]
[TD]Amount American number as text[/TD]
[TD]Currency[/TD]
[TD]Amount European number[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015[/TD]
[TD]9/16/2015 0:09,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015[/TD]
[TD]9/16/2015 0:15,100[/TD]
[TD]100[/TD]
[TD]USD[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]16.09.2015[/TD]
[TD]9/16/2015 5:54,0.30015[/TD]
[TD]0.30015[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,30015[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/16/2015 14:34,100[/TD]
[TD]100[/TD]
[TD]NOK[/TD]
[TD="align: right"]100[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/16/2015 17:51,4800[/TD]
[TD]4800[/TD]
[TD]NOK[/TD]
[TD="align: right"]4800[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/16/2015 18:19,1000[/TD]
[TD]1000[/TD]
[TD]NOK[/TD]
[TD="align: right"]1000[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/16/2015 20:19,0.2699[/TD]
[TD]0.2699[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,2699[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/16/2015 21:04,0.756[/TD]
[TD]0.756[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,756[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 5:30,2015[/TD]
[TD]2015[/TD]
[TD]NOK[/TD]
[TD="align: right"]2015[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 6:50,200[/TD]
[TD]200[/TD]
[TD]NOK[/TD]
[TD="align: right"]200[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 7:40,900[/TD]
[TD]900[/TD]
[TD]NOK[/TD]
[TD="align: right"]900[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 8:49,0.52[/TD]
[TD]0.52[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,52[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 8:53,0.37501792[/TD]
[TD]0.37501792[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,37501792[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 11:07,0.02707997[/TD]
[TD]0.02707997[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,02707997[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 11:26,0.80150239[/TD]
[TD]0.80150239[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,80150239[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 11:28,0.0724[/TD]
[TD]0.0724[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,0724[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]17.09.2015[/TD]
[TD]9/17/2015 11:28,0.5[/TD]
[TD]0.5[/TD]
[TD]LTC[/TD]
[TD="align: right"]0,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 12:08,0.1051[/TD]
[TD]0.1051[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,1051[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 12:27,0.4[/TD]
[TD]0.4[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,4[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 16:30,2500[/TD]
[TD]2500[/TD]
[TD]NOK[/TD]
[TD="align: right"]2500[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 16:33,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 17:40,1.5[/TD]
[TD]1.5[/TD]
[TD]XBT[/TD]
[TD="align: right"]1,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 17:55,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 17:57,500[/TD]
[TD]500[/TD]
[TD]NOK[/TD]
[TD="align: right"]500[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 17:59,25600[/TD]
[TD]25600[/TD]
[TD]NOK[/TD]
[TD="align: right"]25600[/TD]
[TD]Deposit[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 18:01,0.025[/TD]
[TD]0.025[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,025[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/17/2015 19:46,1.027[/TD]
[TD]1.027[/TD]
[TD]XBT[/TD]
[TD="align: right"]1,027[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/18/2015 7:51,0.43199316[/TD]
[TD]0.43199316[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,43199316[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/18/2015 8:22,0.5[/TD]
[TD]0.5[/TD]
[TD]LTC[/TD]
[TD="align: right"]0,5[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/18/2015 9:37,0.0137[/TD]
[TD]0.0137[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,0137[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]18.09.2015[/TD]
[TD]9/18/2015 10:08,0.1[/TD]
[TD]0.1[/TD]
[TD]XBT[/TD]
[TD="align: right"]0,1[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]19.09.2015[/TD]
[TD]9/18/2015 14:31,2.3836[/TD]
[TD]2.3836[/TD]
[TD]XBT[/TD]
[TD="align: right"]2,3836[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]19.09.2015[/TD]
[TD]9/18/2015 15:05,600[/TD]
[TD]600[/TD]
[TD]NOK[/TD]
[TD="align: right"]600[/TD]
[TD]Deposit[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Your new data sample has only withdrawals for XBT (BTC), no deposits. Is this correct?

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
895
Aadya
A
M
  • Question Question
SLA Time Calculation
Replies
5
Views
344
felixstraube
F

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

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