Posts by alansidman

  • Help with creating VBA to clean up and organize some data

    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.

  • Help with creating VBA to clean up and organize some data

    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: )

  • Calculate Total Times Ticket Open

    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: )

  • Conditional Formatting

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


    I have put the code into the attached

    Files

  • Transpose Data from Rows to Columns

    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.

  • VBA script to extract numbers and place in two separate columns

    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"

  • VBA script to extract numbers and place in two separate 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: )

  • VBA - If Cell is not blank, then copy / paste cell to range in same row

    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.

  • Match date in cell to the same date in a range and return value from cell below

    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.