Quick Macro to Reformat Data into specific format

Hi All,

I'm incredibly new to VBA (still) and could use some help with this!

I've attached a file that shows some data in Sheet 1 that I need to be output into Sheet 2 quickly by a macro. There will be thousands of rows of data like this, but they will be together.

The relevant columns are A, F, and G, as they contain the data that needs to be in the output.

The format of the output I need is in Sheet 2, with the following specifics:

Column A - needs to be in "000#" format so if the input value is a "3", then the output needs to be "0003"
Column B - There will be a 6 digit number from the F column in Sheet 1, that needs to be output to "####-##-##", so basically adding dashes between the numbers.

Can anyone help me with this? I would greatly appreciate it.

Thanks!

Try this...

Sub Reformat_Data()
    
    Dim v As Variant, i As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
    
        .Columns("A:A").Value = Sheets("Sheet1").Columns("A:A").Value
        .Columns("A:A").NumberFormat = "0000"
        
        .Columns("B:C").Value = Sheets("Sheet1").Columns("F:G").Value
        .Columns("B:B").NumberFormat = "0000-00-00"
        
        .Columns("A:C").HorizontalAlignment = xlCenter
        .Columns("A:C").ColumnWidth = 13
        
        '*****************************************************
        ' Use this if you want to "convert" (not just display)
        ' the numbers in column B into serial dates.
        With .Range("B2", .Range("B" & Rows.Count).End(xlUp))
            v = .Value2
            On Error Resume Next
            For i = 1 To UBound(v, 1)
                v(i, 1) = CDate(Format(v(i, 1), "0000-00-00"))
            Next i
            On Error GoTo 0
            .Value2 = v
            .NumberFormat = "yyyy-mm-dd"
        End With
        '*****************************************************
        
    End With
    
    Application.ScreenUpdating = True
    
End Sub

This is great and works perfectly.

Thank you very much for the rapid response, I greatly appreciate it. If you are in the bay area, CA I would buy you a drink.

Originally Posted by AlphaFrog

Try this...

Sub Reformat_Data()
    
    Dim v As Variant, i As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
    
        .Columns("A:A").Value = Sheets("Sheet1").Columns("A:A").Value
        .Columns("A:A").NumberFormat = "0000"
        
        .Columns("B:C").Value = Sheets("Sheet1").Columns("F:G").Value
        .Columns("B:B").NumberFormat = "0000-00-00"
        
        .Columns("A:C").HorizontalAlignment = xlCenter
        .Columns("A:C").ColumnWidth = 13
        
        '*****************************************************
        ' Use this if you want to "convert" (not just display)
        ' the numbers in column B into serial dates.
        With .Range("B2", .Range("B" & Rows.Count).End(xlUp))
            v = .Value2
            On Error Resume Next
            For i = 1 To UBound(v, 1)
                v(i, 1) = CDate(Format(v(i, 1), "0000-00-00"))
            Next i
            On Error GoTo 0
            .Value2 = v
            .NumberFormat = "yyyy-mm-dd"
        End With
        '*****************************************************
        
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Hi -

another variation you can consider

Sub test()
Dim rng, rng2, i As Long
rng = Sheet1.Range("a1").CurrentRegion
ReDim rng2(1 To UBound(rng), 1 To 3)
For i = LBound(rng) To UBound(rng)
    rng2(i, 1) = rng(i, 1)
    rng2(i, 2) = IIf(i > 1, Left(rng(i, 6), 4) & "-" & Mid(rng(i, 6), 5, 2) & "-" & Right(rng(i, 6), 2), rng(i, 6))
    rng2(i, 3) = rng(i, 7)
Next
With Sheet2
    .Range("a2").Resize(UBound(rng)).NumberFormat = "000#"
    .Range("b2").Resize(UBound(rng)).NumberFormat = "@"
    .Range("a1").Resize(UBound(rng), 3) = rng2
End With
Erase rng: Erase rng2
End Sub

Regards,
Event

Hi to both - thanks for the awesome solutions!!

I have another added request that makes it slightly more complicated.

In some of the data, there are 2 entries for a given day. Is there any way for the macro to consolidate all of the values for a given Number (col A) and Week (col B)?

I updated the example and highlighted the areas to show what I'm talking about.

Thanks again for all of your assistance - it is greatly appreciated.

I have another question - can the macro be modified such that it only outputs the rows where column C equals a specific value (let's say 4567) AND column D equals a specific value (let's say "Cheese")?

Thank you again!

Hi All,

Would those 2 requests be a complex modification? Right now I can't figure out how filtering and consolidating the specific entries would work out.

Hi -

I would suggest you close this thread and start a new one with your new sample since you have already confirmed that this post had been solved.

Regards,
Event