Nth transaction by weeks

[indygo]

Hi,

I'm trying to calculate how many nth transactions there were in a given week number.


In column A below we have ID number and on the right hand side number of transactions in each week. I would like to split them and calculate how many of those ID's made 1st,2nd,3rd,4th transaction in a given week.
The tricky part is that one can make for example 2 transactions the same week and that complicates things. For example in Week1 we had 2 first transactions, and one 2nd transaction. In Week2 we had one 2nd transaction and one 3rd and so on.

Any idea what should I put in summary table to calculate it that way?

	a	b	c	d	e	f	g
1	ID_number	Week1	Week2	Week3	Week4	Week5
2	111	1	1	2
3	112	2	1	3
4
5
6
7
8

Here is the summary table I'm trying to create, not sure how to proceed with this:

	1st transaction	2nd transaction	3rd transaction	4th transaction
Week1	2	1	1
Week2		1	1
Week3			1	2

 

[Anthony47]

In case you are still interested in this problem, let me suggest a User Defined Function, corresponding to the following code:

Function WSplit(ByRef TData As Range) As Variant
Dim oArr(), LisCnt As Long, TranCnt As Long
Dim iCol As Long, iRow As Long
'
iRow = Application.Caller.Rows.Count
iCol = Application.Caller.Columns.Count
ReDim oArr(1 To iRow, 1 To iCol)
If iRow > TData.Columns.Count Then iRow = TData.Columns.Count
For K = 1 To iCol
    For I = 1 To iRow
        For J = 1 To TData.Rows.Count
            If Application.WorksheetFunction.Sum(TData.Cells(J, 1).Resize(1, I).Value) >= K Then
                oArr(I, K) = oArr(I, K) + 1
            End If
        Next J
    Next I
Next K
For J = 1 To iCol
summa = 0
    For I = 1 To iRow  
        oArr(I, J) = oArr(I, J) - summa
        summa = summa + oArr(I, J)
    Next I
Next J
WSplit = oArr
End Function

Copy the code in a Standard Module of your Vba Project.

Then return to Excel

Suppose that your output table Columns Headers (1st transaction /2nd transaction/ .. /5th transaction) are from O1 to S1; therefore your Row Headers (Week1 /Week2 /Week3 /Week4) will be in N2 to N5, and the output table data area will be O2 to S5
Select the output table data area, thus O2 to S5
Type the following formula into the Formula Bar:

=WSplit(B2:F8)

Confirm the formula using the keys Cont+Shift+Enter (not Enter alone); do this even if your Excel version supports "Dynamic Arrays"

Some results should materialize... Check if they look correct

The general syntax for WSplit is
WSplit(TheSourceTable)
TheSourceTable can be in the same sheet where the formula is located (eg: =WSplit(B2:F8)) or in anothe sheet (eg =WSplit(Sheet1!B2:F8))

The output Columns represent in sequence the 1st, 2nd, 3rd, etc occourrence of the event; and the Rows represent in sequence the Week1 slot, Week2 slot, etc. This is regardless what you write in the columns and rows headers.

You may extend the area of the output table, for example from O2:S5 up to O2:T5 to include calculation of the 6th event:
-select the full new area (O2:T5)
-press F2
-confirm the formula using Contr-Shift-Enter

However you may not reduce the area: you have to clear the formula over the current range of cells and insert the new one in the new range of cells

This is the standard Excel procedure for "array formulas"

Give it a try...

 

[Anthony47]

A demo workbook can be downloaded from here: 4INDYGO-MrEx_Demo_C00924.xlsm

It demonstrates the use of the function WSplit, but also a possible solution based on formulas, by using intermediate helping formulas:
-with reference to the image
In B11 =SUM($B2:B2)
Then copied down to B17 and right to col G

In B21 =SUM(--(B$11:B$17>=$A21))
Then copied down to B31

In C21 =SUM(--(C$11:C$17>=$A21))-SUM($B21:B21)
Then copied down ro C31 and right to col G

Bye

 

[indygo]

Thank you both!