subtotals - count of members on each row, including summary row, but not counting summary

M

mjd

Board Regular
Joined
Feb 23, 2010
Messages
73
hello,

got a goofy one here. I need to include a count of items in a subtotal, that is listed on each line of the subtotal, including the rollup itself, without factoring the summary data:

[TABLE="width: 736"]
<tbody>[TR]
[TD]Tranaction[/TD]
[TD]account[/TD]
[TD]security[/TD]
[TD]price[/TD]
[TD]broker[/TD]
[TD]shares[/TD]
[TD]BlockNumber[/TD]
[TD]need to return value in[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]BLOCK[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD] g5 (3)[/TD]
[TD]summary row[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a1[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD]g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a2[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD] g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a3[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD] g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]BLOCK[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD]g10 (4)[/TD]
[TD]summary row[/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a1[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a2[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a3[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a4[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the above, i need to return the largest block number for each trade in the last colum, so 3 for rows 2-5, and 4 for rows 6-10

in a real scenario, these blocks could be 20+ rows, and there could be multiple blocks for the same security, just with different brokers or at different prices. No blocks will ever be guaranteed to be uniform in size.

i have messed around with a bunch of max/vlookup hybrids, but its everything has just returned the highest value in the column, regardless of the trade details. ultimately, this will need to be processed in a vba macro

Is this possible to do? i feel like it should be, but its driving me crazy. any help would be greatly appreciated.

Thanks!
Mike
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
mjd said:
hello,

got a goofy one here. I need to include a count of items in a subtotal, that is listed on each line of the subtotal, including the rollup itself, without factoring the summary data:

[TABLE="width: 736"]
<tbody>[TR]
[TD]Tranaction[/TD]
[TD]account[/TD]
[TD]security[/TD]
[TD]price[/TD]
[TD]broker[/TD]
[TD]shares[/TD]
[TD]BlockNumber[/TD]
[TD]need to return value in[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]BLOCK[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD] g5 (3)[/TD]
[TD]summary row[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a1[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD]g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a2[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD] g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a3[/TD]
[TD]aapl[/TD]
[TD="align: right"]101.01[/TD]
[TD]abcd[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD] g5 (3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]BLOCK[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD]g10 (4)[/TD]
[TD]summary row[/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a1[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a2[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a3[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sell[/TD]
[TD]a4[/TD]
[TD]xom[/TD]
[TD="align: right"]50.05[/TD]
[TD]poiu[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD] g10 (4)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the above, i need to return the largest block number for each trade in the last colum, so 3 for rows 2-5, and 4 for rows 6-10

in a real scenario, these blocks could be 20+ rows, and there could be multiple blocks for the same security, just with different brokers or at different prices. No blocks will ever be guaranteed to be uniform in size.

i have messed around with a bunch of max/vlookup hybrids, but its everything has just returned the highest value in the column, regardless of the trade details. ultimately, this will need to be processed in a vba macro

Is this possible to do? i feel like it should be, but its driving me crazy. any help would be greatly appreciated.

Thanks!
Mike
Click to expand...

Hi Mike,

This doesn't have to be done in a VBA macro..

If you're happy to add 2 columns and hide one of them...

(Haven't found a way to do it in less.. yet!)


Excel 2010
ABCDEFGHI
TransactionaccountsecuritypricebrokersharesBlockNumberneed to return value in
BuyBLOCKaaplabcd
Buya1aaplabcd

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]HIDE ME[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

</tbody>
TWO FORMULAE​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=IF(B3="BLOCK",I2,"")[/TD]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=IF(B2="BLOCK",0,I1+1)[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Paste in respective cells and then copy down!

NB: with the formulae above, you'll need to make sure you have one "BLOCK" in a cell one row AFTER the data ends.

e.g. say the data ends on row 100... put the word BLOCK into cell B101 (otherwise that last total won't be done)


NOTE: I just realised the "extra" column is already in your worksheet, called "BlockNumber". Ignore the column called hide me, and change the formula to the BlockNumber column!




Alternatively.. you could use this code

Please edit CONSTs to correct sheetname and range to paste if necessary. Currently set to "SHEET5"


Code: 
Const SheetName = "Sheet5"
Const DataStartCell = "A2"
Const NoOfColumnsOfData = 7
Const ColToPlaceDataIn = "H2"


Const DataMaxRow = 1048575


Sub AddCount()


    Dim DataStartRow As Integer, DataStartCol As Integer
    Dim TgtStartRow As Integer, TgtStartCol As Integer
    Dim RetValCntr As Integer, Cntr As Long, LastRow As Long
    
    Dim SrcData As Range
    
    Application.ScreenUpdating = False
    


    DataStartRow = Range(DataStartCell).Row
    DataStartCol = Range(DataStartCell).Column
 
    TgtStartRow = Range(ColToPlaceDataIn).Row
    TgtStartCol = Range(ColToPlaceDataIn).Column
 
 
    LastRow = Sheets(SheetName).Range(Cells(DataStartRow, DataStartCol).Address, Cells(DataMaxRow, DataStartRow).Address).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Set SrcData = Sheets(SheetName).Range(Cells(DataStartRow, DataStartCol).Address & ":" & Cells(LastRow, DataStartCol).Address)
    
    For Cntr = DataStartRow To LastRow
        If SrcData.Range(Cells(Cntr, DataStartRow).Address).Value = "BLOCK" Then
            If RetValCntr > 0 Then
                SrcData.Range(Cells(Cntr - 1, TgtStartCol).Address).Value = RetValCntr
            End If
            RetValCntr = 0
          Else
            RetValCntr = RetValCntr + 1
        End If
    Next Cntr
    
    Set SrcData = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub
 
Last edited:
Upvote 0
Here's a spreadsheet with both versions (one sheet does the macro, the other does the formula): https://www.dropbox.com/s/2nmql71lfan11yn/GoofyOne ToughSubtotalCount.xlsm?dl=0

It's just one formula you need:


Excel 2010
ABCDEFGH
TransactionaccountsecuritypricebrokersharesBlockNumberneed to return value in
BuyBLOCKaaplabcd
Buya1aaplabcd
Buya2aaplabcd
BuyBLOCKaaplabcd

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]

</tbody>
ONE FORMULA​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(OR(B3="BLOCK",LEN(B3)=0),G2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Marty,

Thank you very much for your responses. Unfortunately, I did a bad job of explaining the outcome we need. For each trade, we need a trade level total on each row, detailing how many accounts are in a block trade.

[TABLE="width: 661"]
<tbody>[TR]
[TD]Transaction[/TD]
[TD]account[/TD]
[TD]security[/TD]
[TD]price[/TD]
[TD]broker[/TD]
[TD]shares[/TD]
[TD]BlockNumber[/TD]
[TD]SubAllocations[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]BLOCK[/TD]
[TD]xom[/TD]
[TD]57.00[/TD]
[TD]ghjk[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]a1[/TD]
[TD]xom[/TD]
[TD]57.00[/TD]
[TD]ghjk[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]BLOCK[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a1[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a2[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a3[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a4[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a5[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a6[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sell[/TD]
[TD]a7[/TD]
[TD]aapl[/TD]
[TD]101.01[/TD]
[TD]abcd[/TD]
[TD]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


So, there is a block trade of XOM, with one account. This means there would be a count of 1 in the account row and again in the block summary row. Likewise for the block trade of aapl, seven accounts, so a seven would appear on each row of the block, and again in the summary row.

The most successful i've been on this has been with this formula in Column I2:I
Code: 
=IF(AND(B3="",B2<>""),G2,IF(AND(B3<>"",B2<>"block"),I3,IF(B2="block",H3)))

But it errors out in i3

Any thoughts?

Thanks again for the help, i greatly appreciate it!!
 
Upvote 0
and again in the summary row
Click to expand...


Where is the "summary row"???

A summary row is usually found at the TOP or the BOTTOM of the data...

e.g.

Excel 2010
FGHI
SUMMARY ROW

<tbody>
[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"] 6 [/TD]
[TD="align: right"] 15 [/TD]
[TD="align: right"] 24 [/TD]

</tbody>

​


Are you saying you want it to look like the table you posted.. that that is the OUTCOME you're looking for???

and that BLOCKNUMBER and SUBALLOCATIONS are the two columns you need calculated?



If so.. the following FORMULAE work



Excel 2010
ABCDEFGHI
TransactionaccountsecuritypricebrokersharesBlock NumberSuballocations
BuyBLOCKxomghjk
Buya1xomghjk
SellBLOCKaaplabcd
Sella1aaplabcd
Sella2aaplabcd
Sella3aaplabcd
Sella4aaplabcd
Sella5aaplabcd
Sella6aaplabcd
Sella7aaplabcd

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]HIDE ME[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]57[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]57[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(B2="BLOCK",0,G1+1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=COUNTIF($B$2:B2,"BLOCK")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=MAX(IF($I$2:$I$11=I2,$G$2:$G$11))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


For the formula for cell H2:


paste the formula in... the enter with CTRL+SHIFT+ENTER.

Then COPY the cell..

then select from the NEXT CELL UNDERNEATH (do NOT include it in the range to paste)

So if in H2.. copy H2.. select range H3:Hn​ and then paste.
 
Upvote 0
The summary rows are the ones that say "BLOCK" in column B. The only data i need to calculate is the related row count to populate each cell in column H, suballocations.

In the simplest terms, I need to know how many rows follow each row with "BLOCK" in column B. that figure will be placed in every row until the next BLOCK trade starts and the process restarts.
 
Upvote 0
The only data i need to calculate is the related row count to populate each cell in column H, suballocations.

In the simplest terms, I need to know how many rows follow each row with "BLOCK" in column B. that figure will be placed in every row until the next BLOCK trade starts and the process restarts.
Click to expand...



So you just want the 1-1, 7-7-7-7-7-7-7-7 data in the SUBALLOCATIONS column!


Code: 
Const SheetName = "Sheet5"
Const DataStartCell = "A2"
Const NoOfColumnsOfData = 7
Const ColToPlaceDataIn = "G2"


Const DataMaxRow = 1048575


Sub AddCount()


    Dim DataStartRow As Integer, DataStartCol As Integer
    Dim TgtStartRow As Integer, TgtStartCol1 As Integer, TgtStartCol2 As Integer
    Dim RetValCntr As Integer, Cntr As Long, LastRow As Long
    
    Dim SrcData As Range
    
    Dim Mkr As Integer
    
    Application.ScreenUpdating = False
    


    DataStartRow = Range(DataStartCell).Row
    DataStartCol = Range(DataStartCell).Column
 
    TgtStartRow = Range(ColToPlaceDataIn).Row
    TgtStartCol1 = Range(ColToPlaceDataIn).Column
    TgtStartCol2 = Range(ColToPlaceDataIn).Column + 1
 
    LastRow = Sheets(SheetName).Range(Cells(DataStartRow, DataStartCol).Address, Cells(DataMaxRow, DataStartRow).Address).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Set SrcData = Sheets(SheetName).Range(Cells(DataStartRow, DataStartCol + 1).Address & ":" & Cells(LastRow, DataStartCol).Address)
    
    For Cntr = DataStartRow To LastRow
    
    
        If SrcData.Range(Cells(Cntr - 1, DataStartCol + 1).Address).Value = "BLOCK" Or SrcData.Range(Cells(Cntr - 1, DataStartCol + 1).Address).Value = "" Then
            
            If Mkr > 0 Then
            
                'Place the MAX value into the SUBALLOCATIONS column
                SrcData.Range(Cells(Mkr, TgtStartCol2).Address, Cells(Cntr - 2, TgtStartCol2).Address).Value = RetValCntr
                
            End If
            
            'Reset counter
            RetValCntr = 0
            
            'Place (reset) counter into Block Number
'            SrcData.Range(Cells(Cntr - 1, TgtStartCol1).Address).Value = RetValCntr
            
            'Mark the row where the BLOCK starts
            Mkr = Cntr - 1
          
          Else
            
            'Inc Counter as Account has been found
            RetValCntr = RetValCntr + 1
            
            'Place (incremented) counter into Block Number
'            SrcData.Range(Cells(Cntr - 1, TgtStartCol1).Address).Value = RetValCntr
            
        End If




    Next Cntr
    
    
    If Mkr > 0 Then
            
        'Place the MAX value into the SUBALLOCATIONS column
        SrcData.Range(Cells(Mkr, TgtStartCol2).Address, Cells(Cntr - 2, TgtStartCol2).Address).Value = RetValCntr
        
    End If
    
End Sub




or


Excel 2010
ABCDEFGHI
TransactionaccountsecuritypricebrokersharesBlock NumberSuballocations
BuyBLOCKxomghjk
Buya1xomghjk
SellBLOCKaaplabcd
Sella1aaplabcd
Sella2aaplabcd
Sella3aaplabcd
Sella4aaplabcd
Sella5aaplabcd
Sella6aaplabcd
Sella7aaplabcd

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]HIDE ME[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]57[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]57[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]101.01[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

</tbody>
Sheet5 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=COUNTIF($B$2:B2,"BLOCK")[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]{=MAX(IF($I$2:$I$11=I2,$G$2:$G$11))}[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Either of these (code or formula) should do that.


NB: Obviously, with the formula approach, you will need to change the last row (11) to beyond the number of rows in your data... e.g. $I$2:$I$1000 and $G$2:$G$1000
 
Last edited:
Upvote 0
You're welcome! Sorry I didn't quite understand what you were looking for in the beginning...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Looking for an sequence of consecutive days within an array of dates while ignoring weekends
Replies
6
Views
944
Anonymous1378
A
L
  • Question Question
insert row to add new data for each part
Replies
0
Views
269
leap out
L
Johnny Thunder
  • Question Question
VBA to populate a Summary based on a Matrix of Variables
Replies
1
Views
1K
Johnny Thunder
Johnny Thunder
G
  • Question Question
How to cycle through each row in a column by moving each row individually to another column!
Replies
2
Views
541
gint3232
G
D
  • Question Question
Sum visible cell above + formula in selected row
Replies
1
Views
735
data08
D

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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