Posts by alansidman

    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
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    3. #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    4. #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 8), type number),
    5. #"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"),
    6. #"Filled Up" = Table.FillUp(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7"}),
    7. #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] = "Call")),
    8. #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
    9. in
    10. #"Removed Columns"

    Files

    • Book2.xlsm

      (32.52 kB, downloaded 16 times, last: )

    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?

    Files

    • 12803940.xlsx

      (52.08 kB, downloaded 15 times, last: )

    Here is a UDF developed by Jindon that allows you to do Multi lookups


    I have put the code into the attached

    Files

    An alternative solution that requires no coding is with Power Query


    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.

    Here is the PQ for your updated file.


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Column1], "eFine")),
    4. #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    5. #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.4"})
    6. in
    7. #"Removed Other Columns"

    Power Query solution


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "eFine: 2404589 eFood: 12015" or [Column1] = "eFine: 2404852 eFood: 12827" or [Column1] = "eFine: 2404860 eFood: 12914" or [Column1] = "eFine: 2404892 eFood: 12855")),
    4. #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    5. #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.4"}),
    6. #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1.2", "Test1"}, {"Column1.4", "AMP"}})
    7. in
    8. #"Renamed Columns"

    Files

    • test1.xlsx

      (8.53 kB, downloaded 22 times, last: )

    Patience is important in these types of forums. We are all volunteers that have lives. We work, we sleep, we play and sometimes we participate in these forums. Additionally, this is a world wide forum. We are from many countries and regions. While you may be at work, others may be asleep. Do not bump your thread until at least 24 hours have past. If your need is urgent, you may consider hiring a consultant instead of using this "FREE" forum. Just be patient.

    In order for you to extract the data you wish, you need to have your data in unique columns. You currently have data and dates in the same columns and mixed with the rows. You will need to either normalize your data or write some advanced VBA. Personally, I would prefer to see the data in the form of


    Title--Date--Amount--Change


    Four columns then allow for lots of analysis including Pivot Tables, Look ups, summarizations, etc.