Finding biggest observation conditioned on several factors

[homie]

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:

Timestamp recognized by Excel	Timestamp recognized by Excel, rounded	Remove USD,EUR,NOK or XBT, ETH, LTC from string	Amount American number as text	Currency	Amount European number	Action
16.09.2015 00:09	16.09.2015 00:09	9/16/2015 0:09,100	100	NOK	100	Deposit
16.09.2015 00:15	16.09.2015 00:15	9/16/2015 0:15,100	100	USD	100	Deposit
16.09.2015 05:54	16.09.2015 05:54	9/16/2015 5:54,0.30015	0.30015	XBT	0,30015	Withdraw
16.09.2015 14:34	16.09.2015 14:34	9/16/2015 14:34,100	100	NOK	100	Deposit
16.09.2015 17:51	16.09.2015 17:51	9/16/2015 17:51,4800	4800	NOK	4800	Deposit
16.09.2015 18:19	16.09.2015 18:19	9/16/2015 18:19,1000	1000	NOK	1000	Deposit
16.09.2015 20:19	16.09.2015 20:19	9/16/2015 20:19,0.2699	0.2699	XBT	0,2699	Withdraw
16.09.2015 21:04	16.09.2015 21:04	9/16/2015 21:04,0.756	0.756	XBT	0,756	Withdraw
17.09.2015 05:30	17.09.2015 05:30	9/17/2015 5:30,2015	2015	NOK	2015	Deposit
17.09.2015 06:50	17.09.2015 06:50	9/17/2015 6:50,200	200	NOK	200	Deposit
17.09.2015 07:40	17.09.2015 07:40	9/17/2015 7:40,900	900	NOK	900	Deposit
17.09.2015 08:49	17.09.2015 08:49	9/17/2015 8:49,0.52	0.52	XBT	0,52	Withdraw
17.09.2015 08:53	17.09.2015 08:53	9/17/2015 8:53,0.37501792	0.37501792	XBT	0,37501792	Withdraw
17.09.2015 11:07	17.09.2015 11:07	9/17/2015 11:07,0.02707997	0.02707997	XBT	0,02707997	Withdraw
17.09.2015 11:26	17.09.2015 11:26	9/17/2015 11:26,0.80150239	0.80150239	XBT	0,80150239	Withdraw
17.09.2015 11:28	17.09.2015 11:28	9/17/2015 11:28,0.0724	0.0724	XBT	0,0724	Withdraw
17.09.2015 11:28	17.09.2015 11:28	9/17/2015 11:28,0.5	0.5	LTC	0,5	Withdraw
17.09.2015 12:08	17.09.2015 12:08	9/17/2015 12:08,0.1051	0.1051	XBT	0,1051	Withdraw
17.09.2015 12:27	17.09.2015 12:27	9/17/2015 12:27,0.4	0.4	XBT	0,4	Withdraw
17.09.2015 16:30	17.09.2015 16:30	9/17/2015 16:30,2500	2500	NOK	2500	Deposit
17.09.2015 16:33	17.09.2015 16:33	9/17/2015 16:33,0.025	0.025	XBT	0,025	Withdraw
17.09.2015 17:40	17.09.2015 17:40	9/17/2015 17:40,1.5	1.5	XBT	1,5	Withdraw
17.09.2015 17:55	17.09.2015 17:55	9/17/2015 17:55,0.025	0.025	XBT	0,025	Withdraw
17.09.2015 17:57	17.09.2015 17:57	9/17/2015 17:57,500	500	NOK	500	Deposit

<tbody>
</tbody>

 

[homie]

Your new data sample has only withdrawals for XBT (BTC), no deposits. Is this correct?

M.

Sorry for the late reply, very busy weekend.

Yeah, that is correct. For some reason, our data guy could only extract fiat currency (NOK) deposits (not withdrawels) and only XBT withdrawels (no deposits).

 

[Marcelo Branco]

Ok.
Assuming data sample in Sheet1 columns A:F, headers in row 1, data beginning in row 2
Dates (column A) in ascending order

Sheet2
Criteria --> columns A:D (light blue)
Just for testing purposes i included a row with Withdraw as criteria for XBT

Results --> columns E:L (gray area)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Ref Date​

Days Before​

Currency​

Action​

LDA​

LDA Day​

LAA​

LAA Day​

SDA​

SDA Day​

SAA​

SAA Day​

2​

19/09/2015​

30​

NOK​

Deposit​

6​

17/09/2015​

28600​

18/09/2015​

1​

16/09/2015​

100​

16/09/2015​

3​

19/09/2015​

30​

XBT​

Deposit​

0​

0​

0​

0​

4​

19/09/2015​

30​

XBT​

Withdraw​

10​

18/09/2015​

3,65279​

18/09/2015​

1​

16/09/2015​

0,30015​

16/09/2015​

<tbody>
</tbody>

To get these results run the macro Main

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" '<--adjust the sheet name
    With Sheets(shData)
        lastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rData = .Range("A2:F" & 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(, 8) = Array(0, "", 0, "", 0, "", 0, "")
            Else
                .Range("E" & i).Resize(, 8) = 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, i As Long
    'LDA=LargestNumDepositsAny, LDA=LargestAmountAny
    Dim lLDA As Long, dbLAA As Double
    Dim DayLAA As Variant, DayLDA As Variant
    'SDA=SmallestNumDepositsAny, SAA=SmallestAmountAny
    Dim lSDA As Long, dbSAA As Double
    Dim DaySDA As Variant, DaySAA As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    vData = Range(r.Cells(lFirst, 1), r.Cells(lLast, 6))
    For i = LBound(vData, 1) To UBound(vData, 1)
        If UCase(vData(i, 4)) = UCase(Crit1) And UCase(vData(i, 6)) = 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, 5))
            Else
                dic(vData(i, 1)) = Array(1, vData(i, 5))
            End If
        End If
    Next i
    
    If dic.Count Then
        'Initialize lSDA and dbSAA with Max possible values
        lSDA = UBound(vData, 1)
        dbSAA = Application.Sum(r.Columns(5))
        
        For Each vKey In dic.keys
            If dic(vKey)(0) > lLDA Then
                lLDA = dic(vKey)(0)
                DayLDA = vKey
            End If
            If dic(vKey)(1) > dbLAA Then
                dbLAA = dic(vKey)(1)
                DayLAA = vKey
            End If
            If dic(vKey)(0) < lSDA Then
                lSDA = dic(vKey)(0)
                DaySDA = vKey
            End If
            If dic(vKey)(1) < dbSAA Then
                dbSAA = dic(vKey)(1)
                DaySAA = vKey
            End If
        Next vKey
    End If
    
    GetResults = Array(lLDA, DayLDA, dbLAA, DayLAA, lSDA, DaySDA, dbSAA, DaySAA)
End Function

M.