Help with creating VBA to clean up and organize some data

  • Hey all,


    I'm trying to find a way to create some VBA code to help clean up and organize some data so I can create some reports. The data gets pulled in this format:


    Call
    123456789
    123456789
    Sun, 12/19/2021, 2:35 pm
    0:02
    0:09
    Client Name here
    A No Answer.
    Call
    123456789
    123456789
    Sun, 12/19/2021, 2:35 pm
    0:02
    0:09
    Client Name here
    A No Answer.


    It's call data, and each set of call data is 8 rows, starting with the "Call" row. What I'm trying to do is copy the 7 rows of data below "call" and then paste them in the cell next to "Call" transposed so they string out across the columns. I've tried recording a macro ("TestMacro") of what I was doing, but it puts specific cell references in the code and doesn't work if I rerun the macro.


    I thought I might be able to use ActiveCell and Offset to try to select the data but that doesn't seem to want to work either. Ideally, I'd like to create code that will go through and copy/transpose each set of data into it's own row and cycle through all data until it reaches EOF. Some of these data files are in excess of 500+ calls.


    I've attached a sample workbook with a few sample records on Sheet1, and how I'd like everything to end up on Sheet2. If someone could point me in the direction of the right VBA commands to use I can try to write the code myself. I'm just not finding any help on Google and this was the first forum I came across on a search for Excel 2019 VBA forums.


    Thanks!

  • 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"
  • Alternatively try the attached, click the button on Sheet 1.


    Code assigned to the button:

  • Both of these responses were fantastic! I thank you both for the assistance.. Now I'm going to go sift through the code on both so I can understand how it's working.

    I used to be pretty good at VBA coding but then I took a hiatus and went to chase a dream. Was away from computers for several years and kinda forgot things. But, just like riding a bike I'm sure it will come back the more I dig into it. :)


    Thanks again guys!

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!