Help with creating VBA to clean up and organize some data

cthompson73 · Dec 20th 2021

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

Sun, 12/19/2021, 2:35 pm

0:02

0:09

Client Name here

A No Answer.

Call

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!

**KjBox** · Dec 21st 2021

Why is Row 1 of Sheet1 like it is?

**alansidman** · Dec 21st 2021

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"

**KjBox** · Dec 21st 2021

Alternatively try the attached, click the button on Sheet 1.

Code assigned to the button:

Code

Sub OrganiseData()
    Dim x, y, i As Long, ii As Long, iii As Long
    x = Sheet1.Cells(1).CurrentRegion
    ReDim y(1 To UBound(x, 1) / 8, 1 To 8)
    
    For i = 1 To UBound(x, 1) Step 8
        ii = ii + 1: iv = i - 1
        For iii = 1 To 8
            y(ii, iii) = x(iii, 1)
        Next
    Next
    With Sheet2
        .Cells(1).CurrentRegion.Clear
        With .[a1].Resize(UBound(y, 1), 8)
            .Value = y
            With .Columns(5).Resize(, 2)
                .NumberFormat = "h:mm"
                .HorizontalAlignment = -4131
            End With
            .Columns(8).Font.Color = RGB(107, 165, 58)
        End With
            .Activate
            .Columns.AutoFit
            Application.Goto .[a1]
    End With
    
End Sub

Display More

cthompson73 · Dec 21st 2021

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!

**alansidman** · Dec 21st 2021

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

Participate now!

Share