Copy specific rows based on cell value from two cells

[eotterholt]

I am working on creating a compliance check based on two cell values.

I have a "data dump" worksheet where I dump the data from my source. I comes out like below.
(it's not code, I just used it to highlight it)

                                          Column
         A                               B              C                       D                    E

1    security Type            qty             Security Name        Date           Market Value

2     mmus                    100              BofA                      12/15/15       1000.00

3     mmus                    50                BofA                      3/14/15          750.00

4    mmus                    100              Key Bank               12/15/15       1000.00

5     mmus                    75               BofA                      12/15/15       1000.00

6     oius                     100              Wells Fargo            12/15/15       1000.00

7    mmus                    100              Wells Fargo             12/15/15       1000.00

I have multiple security types, I need to search and return and create a total for all security types where the Security Names match so I would have a total for all MMUS for BofA of 2750

So it would return on another worksheet "Issuer Compliance" the following:

        A                               B              C                       D                    E

1    security Type            qty             Security Name        Date           Market Value

2     mmus                    100              BofA                      12/15/15       1000.00

3     mmus                    50                BofA                      3/14/15          750.00

4     mmus                    75               BofA                      12/15/15       1000.00
-------------------------------------------------------------------------------------------
5                                 225                BofA                                         2750

 
6     oius                     100              Wells Fargo            12/15/15       1000.00

7    mmus                    100              Wells Fargo             12/15/15       1000.00      

4    mmus                    100              Key Bank               12/15/15       1000.00

I'd prefer to use VBA to do the heavy lifting as the worksheets can contain a lot of data, however, if there is a formula solution, I'd look at that too. I just can't wrap my mind around it and am stuck.

Thanks for the help

 

[myall_blues]

Hi

Do you need to retain the individual data lines on your 'Issuer Compliance' worksheet, or would a summary suffice? And is the list of security names and types consistent from one 'dump' to the next.

If so you could set up your issuer compliance worksheet with the various combinations of security type and name and use SUMIFS to calculate the totals:

e.g. Assume your data dump worksheet is 'Sheet1' as depicted in your first code block. Then set up a second sheet (Issuer compliance) as per below.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Security type[/TD]
[TD]Security name[/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]mmus[/TD]
[TD]BofA[/TD]
[TD]225[/TD]
[TD]2750[/TD]
[/TR]
</tbody>[/TABLE]





Formulas are:
in C2: =SUMIFS(Sheet1!B:B,Sheet1!$C:$C,$B2,Sheet1!$A:$A,$A2)
in D2: =SUMIFS(Sheet1!E:E,Sheet1!$C:$C,$B2,Sheet1!$A:$A,$A2)

Add additional security type/name combinations and copy down formulae as appropriate.

Regards

Murray

 

[eotterholt]

Thank you Murray. My challenge is that while security type is a short list of about 35 types, I could have millions of issuers. Also, I couldn't catch a new issuer. So being able to look at the column for issuer and see if there is more than one with the same name and sum those together.

 

[myall_blues]

Fair enough. Try this code as a module in your data dump worksheet then (a bit rough - could be neater)

Let me know if you need help to install and run VBA code.

Regards

Murray

Sub IssuerCompliance()
    '
    ' Create a separate workbook with ordered data and subtotals
    '
    ' Variable declarations - Objects
    '
    Dim MasterSheet As Worksheet
    Dim Wb As Workbook
    Dim Wks As Worksheet
    '
    ' Strings
    '
    Dim txtMasterBook As String
    Dim txtSheetName1 As String
    Dim secType As String, secName As String, secdate As String
    Dim prevSecType As String, prevSecName As String
    '
    ' Integers
    '
    Dim c As Integer
    Dim valOutputRow As Long
    Dim valStartRow As Long
    Dim LastRow As Long, secQty As Long, totalSecQty As Long
    '
    ' Floating
    '
    Dim secValue As Single, totalSecValue As Single
    '
    Application.ScreenUpdating = False
    '
    ' Get current workbook name
    '
    txtMasterBook = ActiveWorkbook.Name
    Set MasterSheet = Workbooks(txtMasterBook).ActiveSheet
    '
    ' Sort the input
    '
    LastRow = MasterSheet.UsedRange.Rows.Count


    ActiveSheet.Range("A1:E" & LastRow).Sort _
            Key1:=ActiveSheet.Columns("A"), Order1:=xlAscending, _
            Key2:=ActiveSheet.Columns("C"), Order2:=xlAscending, _
            Header:=xlYes
    '
    ' Create a new workbook and add a worksheet with required name
    '
    Set Wb = Workbooks.Add
    txtSheetName1 = Format(Date, "yyyymmdd") & Format(Time, "hhnn") & "IssuerCompliance"
    ActiveWorkbook.Sheets.Add.Name = txtSheetName1
    '
    ' Remove unwanted worksheets
    '
    For Each Wks In ActiveWorkbook.Worksheets
        If Wks.Name <> txtSheetName1 Then
            Application.DisplayAlerts = False
            Wks.Delete
            Application.DisplayAlerts = True
        End If
    Next Wks
    '
    ' Transfer headings
    '
    For c = 1 To 5
        Worksheets(txtSheetName1).Cells(1, c).Value = MasterSheet.Cells(1, c).Value
    Next c
    prevSecType = MasterSheet.Cells(2, 1).Value
    prevSecName = MasterSheet.Cells(2, 3).Value
    totalSecQty = 0
    totalSecValue = 0
    '
    ' Transfer records to the new spreadsheet
    '
    valStartRow = 2
    valOutputRow = 2
    '
    ' While there is data in the row
    '
    While MasterSheet.Cells(valStartRow, 1) <> ""
        secType = MasterSheet.Cells(valStartRow, 1).Value
        secQty = MasterSheet.Cells(valStartRow, 2).Value
        secName = MasterSheet.Cells(valStartRow, 3).Value
        secdate = MasterSheet.Cells(valStartRow, 4).Value
        secValue = MasterSheet.Cells(valStartRow, 5).Value
        If valStartRow = 2 Or (secType = prevSecType And secName = prevSecName) Then
            Worksheets(txtSheetName1).Cells(valOutputRow, 1).Value = secType
            Worksheets(txtSheetName1).Cells(valOutputRow, 2).Value = secQty
            Worksheets(txtSheetName1).Cells(valOutputRow, 3).Value = secName
            Worksheets(txtSheetName1).Cells(valOutputRow, 4).Value = secdate
            Worksheets(txtSheetName1).Cells(valOutputRow, 5).Value = secValue
            totalSecQty = totalSecQty + secQty
            totalSecValue = totalSecValue + secValue
        Else
            Worksheets(txtSheetName1).Cells(valOutputRow, 1).Value = prevSecType
            Worksheets(txtSheetName1).Cells(valOutputRow, 2).Value = totalSecQty
            Worksheets(txtSheetName1).Cells(valOutputRow, 3).Value = prevSecName
            Worksheets(txtSheetName1).Cells(valOutputRow, 4).Value = ""
            Worksheets(txtSheetName1).Cells(valOutputRow, 5).Value = totalSecValue
            totalSecQty = 0
            totalSecValue = 0
            prevSecType = secType
            prevSecName = secName
            valOutputRow = valOutputRow + 2
            Worksheets(txtSheetName1).Cells(valOutputRow, 1).Value = secType
            Worksheets(txtSheetName1).Cells(valOutputRow, 2).Value = secQty
            Worksheets(txtSheetName1).Cells(valOutputRow, 3).Value = secName
            Worksheets(txtSheetName1).Cells(valOutputRow, 4).Value = secdate
            Worksheets(txtSheetName1).Cells(valOutputRow, 5).Value = secValue
            totalSecQty = totalSecQty + secQty
            totalSecValue = totalSecValue + secValue
        End If
        valStartRow = valStartRow + 1
        valOutputRow = valOutputRow + 1
    Wend
    '
    ' Write out totals for the last type
    '
    Worksheets(txtSheetName1).Cells(valOutputRow, 1).Value = prevSecType
    Worksheets(txtSheetName1).Cells(valOutputRow, 2).Value = totalSecQty
    Worksheets(txtSheetName1).Cells(valOutputRow, 3).Value = prevSecName
    Worksheets(txtSheetName1).Cells(valOutputRow, 4).Value = ""
    Worksheets(txtSheetName1).Cells(valOutputRow, 5).Value = totalSecValue
End Sub