Posts by alansidman

    I would bring the text tile into Power Query which is called Get and Transform Data in later versions of Excel. Once in the PQ Editor, you can change the date from text to Date format.


    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    Try this:

    Since you are using 365, you can read a PDF by bringing it into Power Query called Get and Transform Data and found on the Data Tab. Click on Data-->Get Data-->From File-->PDF. It will open in the PQ Editor. If you don't wish to make any changes to it, then click on Load and Close and it will bring it directly into Native Excel.


    To get this info from a web site, use the same methodology but select From Web and enter the URL.

    This a very easy task if you are willing to use Power Query. To demo this, instead of a picture (which I cannot manipulate), please upload your sample file.


    Grouping is one of the simplest tasks that you can perform in Power Query which is on your Data Tab of the Ribbon. In your version, it is called Get and Transform Data.

    An alternative is to employ Power Query


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Last Takeoff EGTHDM SMOOTH current week]<=10 then 1 else 0),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
    in
    #"Removed Columns"

    Here is a SQL statement I created using the data in an Access DB You should be able to adapt to MYSQL


    SQL
    SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER
    FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK
    GROUP BY Table1.COMPANY, Table2.TRUCK;


    Putting in VBA format


    Code
    strSql = "SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER " & vbCrLf & _
    "FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK " & vbCrLf & _
    "GROUP BY Table1.COMPANY, Table2.TRUCK;"

    An alternative to VBA is Power Query. Here is the Mcode and file attached.


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Column1]) is number otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = true then [Column1] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
    in
    #"Removed Columns"

    You may not need VBA. Depending upon which version of Excel you are using, FlashFill may be an option available to you.


    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    To help you understand PQ:

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Can be achieved with Power Query.


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 8), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] = "Call")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
    in
    #"Removed Columns"

    Can be accomplished with Power Query/Get and Transform Data


    File attached for review. Note that you have approx 40 rows with no reporting date. How would you like those handled?