Macro to Filter data based on 4 Criteria's and move filtered data into another sheet

[abhay_547]

I have worksheet which consists of multiple columns of data, in this worksheet I have below 4 columns on which I need to apply following filter logic and if the filter logic matches then I need to move those rows of data into a different worksheet.

Columns on which the criteria needs to be applied: (Note: There are multiple other columns in the actual worksheet but in the sample data I have just included the ones which are required to apply the criteria)
Column 1: Client Name
Column 2: Price
Column 3: Quantity
Column 4: Label

Below is my filter Criteria:
Label = Trade Alloc or Trade Exec
Quantity = Sumtotal of Trade Alloc = Sumtotal of Trade Exec rows
Price = Same betweenTrade Alloc and Trade Exec .i.e. Trade Alloc = Trade Exec Rows
Client Name = Same between Trade Alloc and Trade Exec .i.e. Trade Alloc = Trade Exec Rows

Attached is the sample data.
First Sheet: Sample Rec Data
Second Sheet: Sample Filtered Data


Now I have below SQL Statement from my MS Access Database, can we incorporate similar thing in ms excel macro. but in the below case it also excludes the rows which shouldn't be captured in output for e.g. if the total sum of Qty column for Trade Exec is 10 and Total Sum of Trade Alloc is 22 then Macro should take only those rows for which the sum total of Trade Alloc label items is 10 and copy the same to next sheet.

SELECT [Sample Rec Data].[Name of Client], [Sample Rec Data].Price, Sum([Sample Rec Data].Qty) AS SumOfQty, [Sample Rec Data].Label
FROM [Sample Rec Data]
GROUP BY [Sample Rec Data].[Name of Client], [Sample Rec Data].Price, [Sample Rec Data].Label
HAVING ((([Sample Rec Data].Label)='TRADE ALLOC' Or ([Sample Rec Data].Label)='TRADE EXEC'));

I have got the below code so far but need help to incorporate the above sql statement into below macro.

Sub sbADOExample()

'Using Microsoft Excel 2013
'Reference Microsoft ActiveX Data Objects 2.8 Library

Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String



DBPath = ThisWorkbook.FullName


sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"



Conn.Open sconnect
    
    'sSQLSting = "SELECT * From [Sample Rec Data$]"
   sSQLSting = "SELECT [Sample Rec Data$].[Name of Client], [Sample Rec Data$].Price,[Sample Rec Data].Label, SUM([Sample Rec Data].[Qty]) " & _
         "FROM [Sample Rec Data$]" & _
         "GROUP BY [Sample Rec Data$].[Name of Client], [Sample Rec Data$].Price, [Sample Rec Data$].Label"
         
        
    mrs.Open sSQLSting, Conn
     
        Sheets("Sample Filtered Data").Range("A2").CopyFromRecordset mrs
   
    mrs.Close


Conn.Close

End Sub

[vbfbryce]

Why is this row on the first tab:

Altavista Pty Ltd 125 3 Trade Alloc

not moved to the second tab?

Do you want ALL rows that have a label of Trade Exec or Trade Alloc to move to the second sheet?

EDIT: Probably has something to do with your "sum of qty" criteria?

[abhay_547]

Why is this row on the first tab:



not moved to the second tab?

Do you want ALL rows that have a label of Trade Exec or Trade Alloc to move to the second sheet?

EDIT: Probably has something to do with your "sum of qty" criteria?

Nope, I have pasted the desired result in second tab .i.e. Sample Filtered Data and if you look at the first example below. Now if you see the sum Total of Qty column for Trade Alloc row items is 9 and for Trade Exec also it's 9. There was one more Trade Alloc row item with Qty value 3 and it was excluded. so basically I want to include only row items where the sum total of Qty column of Trade Alloc row items and Trade Exec row items is same.

Name of Client Price Qty Label
Altavista Pty Ltd 125 1 Trade Alloc
Altavista Pty Ltd 125 8 Trade Alloc
Altavista Pty Ltd 125 2 Trade Exec
Altavista Pty Ltd 125 5 Trade Exec
Altavista Pty Ltd 125 2 Trade Exec

[westconn1]

can trade exec be > trade alloc? or always trade alloc is >= to trade exec?

[abhay_547]

can trade exec be > trade alloc? or always trade alloc is >= to trade exec?

yes they can be, but the price would be different so the sum total of Qty of Trade Alloc and Trade Exec line items should always match for a particular price and client name.

may be we can try below if the data compared being in same sheet is an issue:

If we paste the same data in 2 sheets and compare Trade Alloc rows sumtotal (where price and client name is same) from the first sheet with second sheet where the Trade Exec rows sumtotal is equal to Trade Alloc (where price and client name is same) and then copy matching Trade Alloc rows from first sheet and Trade Exec rows from second sheet into a third.

[abhay_547]

Hi westconn1 / vbfbryce, Any luck with the above macro code

[westconn1]

afaik you can not do a sql to return a list of transactions to equal to a given total
though you could easily do a sum of both trade alloc and tradeexec for a client, and if equal, simple do the rest

if not equal then
assuming that tradealloc is always > for each client:-
the only way i would know to do it is to get a recordset to sum the qty of tradealloc for each client, then loop a recordset of tradeexec for the same client, to try to match values

someone in the database forum may have better suggestions