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:

TranactionaccountsecuritypricebrokersharesBlockNumberneed to return value in
BuyBLOCKaapl101.01abcd200 g5 (3)summary row
Buya1aapl101.01abcd101g5 (3)
Buya2aapl101.01abcd62 g5 (3)
Buya3aapl101.01abcd43 g5 (3)
sellBLOCKxom50.05poiu500g10 (4)summary row
sella1xom50.05poiu201 g10 (4)
sella2xom50.05poiu152 g10 (4)
sella3xom50.05poiu103 g10 (4)
sella4xom50.05poiu54 g10 (4)

<tbody>
</tbody>

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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:

TranactionaccountsecuritypricebrokersharesBlockNumberneed to return value in
BuyBLOCKaapl101.01abcd200 g5 (3)summary row
Buya1aapl101.01abcd101g5 (3)
Buya2aapl101.01abcd62 g5 (3)
Buya3aapl101.01abcd43 g5 (3)
sellBLOCKxom50.05poiu500g10 (4)summary row
sella1xom50.05poiu201 g10 (4)
sella2xom50.05poiu152 g10 (4)
sella3xom50.05poiu103 g10 (4)
sella4xom50.05poiu54 g10 (4)

<tbody>
</tbody>

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
1TransactionaccountsecuritypricebrokersharesBlockNumberneed to return value inHIDE ME
2BuyBLOCKaapl101.01abcd2000
3Buya1aapl101.01abcd1011

<tbody>
</tbody>
TWO FORMULAE​

Worksheet Formulas
CellFormula
H2=IF(B3="BLOCK",I2,"")
I2=IF(B2="BLOCK",0,I1+1)

<tbody>
</tbody>

<tbody>
</tbody>



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
1TransactionaccountsecuritypricebrokersharesBlockNumberneed to return value in
2BuyBLOCKaapl101.01abcd200
3Buya1aapl101.01abcd101
4Buya2aapl101.01abcd622
5BuyBLOCKaapl101.01abcd40

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
ONE FORMULA​

Worksheet Formulas
CellFormula
H2=IF(OR(B3="BLOCK",LEN(B3)=0),G2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
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.

TransactionaccountsecuritypricebrokersharesBlockNumberSubAllocations
BuyBLOCKxom57.00ghjk2001
Buya1xom57.00ghjk2011
SellBLOCKaapl101.01abcd7007
Sella1aapl101.01abcd1017
Sella2aapl101.01abcd1027
Sella3aapl101.01abcd1037
Sella4aapl101.01abcd1047
Sella5aapl101.01abcd1057
Sella6aapl101.01abcd1067
Sella7aapl101.01abcd1077

<tbody>
</tbody>


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
20JanFebMar
21147
22258
23369
24SUMMARY ROW61524

<tbody>
</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
1TransactionaccountsecuritypricebrokersharesBlock NumberSuballocationsHIDE ME
2BuyBLOCKxom57ghjk20011
3Buya1xom57ghjk20111
4SellBLOCKaapl101.01abcd70072
5Sella1aapl101.01abcd10172
6Sella2aapl101.01abcd10272
7Sella3aapl101.01abcd10372
8Sella4aapl101.01abcd10472
9Sella5aapl101.01abcd10572
10Sella6aapl101.01abcd10672
11Sella7aapl101.01abcd10772

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5​

Worksheet Formulas
CellFormula
G2=IF(B2="BLOCK",0,G1+1)
I2=COUNTIF($B$2:B2,"BLOCK")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
H2{=MAX(IF($I$2:$I$11=I2,$G$2:$G$11))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


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
1TransactionaccountsecuritypricebrokersharesBlock NumberSuballocationsHIDE ME
2BuyBLOCKxom57ghjk20011
3Buya1xom57ghjk20111
4SellBLOCKaapl101.01abcd70072
5Sella1aapl101.01abcd10172
6Sella2aapl101.01abcd10272
7Sella3aapl101.01abcd10372
8Sella4aapl101.01abcd10472
9Sella5aapl101.01abcd10572
10Sella6aapl101.01abcd10672
11Sella7aapl101.01abcd10772

<tbody>
</tbody>
Sheet5 (3)​

Worksheet Formulas
CellFormula
I2=COUNTIF($B$2:B2,"BLOCK")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
H2{=MAX(IF($I$2:$I$11=I2,$G$2:$G$11))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


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
646
Anonymous1378
A
L
  • Question
insert row to add new data for each part
Replies
0
Views
224
leap out
L
Johnny Thunder
  • Question
VBA to populate a Summary based on a Matrix of Variables
Replies
1
Views
993
Johnny Thunder
Johnny Thunder
G
  • Question
How to cycle through each row in a column by moving each row individually to another column!
Replies
2
Views
481
gint3232
G
D
  • Question
Sum visible cell above + formula in selected row
Replies
1
Views
631
data08
D

Forum statistics

Threads
1,216,411
Messages
6,130,440
Members
449,581
Latest member
econtent2

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