How to change non-tabular data into tabular data with VBA

[waimea]

Hi, I have a file with non-tabular data and I want to loop through the file and change or use the current file format into an user form.

All suggestions on how to move forward with this non-tabular data is appreciated!

I think that I need some kind of loop that goes through every page and creates tabular data from the non-tabular data.

I would like some help with where to start to transform the data and see if I can figure out the rest of the code!

[waimea]

I want to get column1 = Company name, column2 = Register, column3 = order etc.

Should I use offset? All suggestions are welcome. I don't know where to start tackling this non-tabular file?

[Paul_Hossler]

Well, assuming that your data is consistant (doesn't have to be but makes it easier), what I'd do is ...

1. Add new blank worksheet ("Output')
2. Add column headers
3. Dim output line colunter = 2

4. Find last row number in 'Input'
5. Dim input line counter
6. Loop from 1 to LastRow

Dim iOut as long, iIn as long, iLastIn as Long


If Worksheets("Input").Cells (iIn,1).value = "Company" then
       iOut = iOut + 1
       Worksheets("Output").Cells (iOut,1).value = Worksheets("Input").Cells (iIn,2).value
   EndIf

   If Worksheets("Input").Cells (iIn,1).value = "Register" then
       Worksheets("Output").Cells (iOut,2).value = Worksheets("Input").Cells (iIn,2).value
   EndIf
   etc.

   ' 2 on one line
   If Worksheets("Input").Cells (iIn,1).value = "Misc Namer" then
       Worksheets("Output").Cells (iOut,8).value = Worksheets("Input").Cells (iIn,2).value

       If Worksheets("Input").Cells (iIn,6).value = "Code:" then
           Worksheets("Output").Cells (iOut,9).value = Worksheets("Input").Cells (iIn,7).value
       Endif

   EndIf

Next

Not perfect, but possible start

[waimea]

Hi Paul, thank you for your reply.

I will start with your code and see where I end up.

My data is consistent so once everything is mapped I want to loop it.

What kind of loop should I use?

[Paul_Hossler]

What kind of loop should I use?

For iIn = 1 to LastRow

…..



Next iIn

[waimea]

Thank you for your reply Paul!

I am having trouble with implementing your suggested steps.

If you have the time could you please post the entire code as per your suggestions with steps 1 to 6?

[Paul_Hossler]

I would like some help with where to start to transform the data and see if I can figure out the rest of the code!

If you have the time could you please post the entire code as per your suggestions with steps 1 to 6?

Sure, post a workbook with 10 - 20 sets of data to play with

If there's any sensitive data, don't clear it, but replace with something else

[waimea]

Hi Paul,

I was asking for you to write the loop and to show how I can add the code for the different parts of the data that I want to transform.

The data is consistent and I would like to code and learn something new at the same time, so I am not asking for you to do all of it for me.

I was thinking that you could show me how to:

1. Add new blank worksheet ("Output')
2. Add column headers
3. Dim output line colunter = 2
4. Find last row number in 'Input'
5. Dim input line counter
6. Show me how to add/map the contains of the file for the loop
7. Loop from 1 to LastRow

So when I have working code I can try to transform the data!

[Paul_Hossler]

No problem, struggling is the best way to learn; that and Google

Here's a skeleton macro

Option Explicit

'1. Add new blank worksheet ("Output')
'2. Add column headers
'3. Dim output line colunter = 2
'4. Find last row number in 'Input'
'5. Dim input line counter
'6. Loop from 1 to LastRow


Sub Example()
    Dim iOut As Long, iIn As Long, iLastIn As Long
    Dim wsIn As Worksheet, wsOut As Worksheet
    
    'delete old Out
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Output").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'add new Output
    Worksheets.Add
    ActiveSheet.Name = "Output"
    
    'sets and inits
    Set wsIn = Worksheets("Input")
    Set wsOut = Worksheets("Output")
    iOut = 1
    iLastIn = wsIn.UsedRange.Rows.Count
    
    
    'output column headers
    With wsOut
        .Cells(iOut, 1).Value = "Company"
        .Cells(iOut, 2).Value = "Register"
        .Cells(iOut, 3).Value = "Order"
        
        'etc
    End With
    
    With wsIn
        For iIn = 2 To iLastIn
    
            If .Cells(iIn, 1).Value = "Company" Then
                   iOut = iOut + 1
                   wsOut.Cells(iOut, 1).Value = .Cells(iIn, 2).Value
               End If
            
               If .Cells(iIn, 1).Value = "Register" Then
                   wsOut.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
               End If
            
               'etc.
            
               ' 2 on one line
               If .Cells(iIn, 1).Value = "Misc. Name" Then
                   wsOut.Cells(iOut, 8).Value = .Cells(iIn, 2).Value
            
                   If .Cells(iIn, 6).Value = "Code:" Then
                       wsOut.Cells(iOut, 9).Value = .Cells(iIn, 7).Value
                   End If
            
               End If
            
    
                'etc
        Next iIn
    End With
End Sub

[waimea]

Hi Paul,

this is very kind of you. I am going to try it a once.

Thank you for your reply and for your skeleton code!

[waimea]

Now I have the code working, thank you very much Paul!

How would I go about opening this file and run the macro?

1. Open file and run macro?
2. Run macro on closed file?

I would like to import Input into my master workbook, run the macro on it and then export Output to a new sheet.

Sub OpenFile()
Dim file As Variant


file = Application.GetOpenFilename(, , "Open Exel file)


End Sub

[waimea]

Any suggestions on how I should proceed with this code?

I have googled but most tutorials seem to use CSV/text files.

[Paul_Hossler]

Not hard, but I have some questions about which is where. Is this correct?

Before:

a. Master.xlsm with the macros, no Input sheet and no Output sheet
b. Data.xlsx with Input sheet


After:

a. Master.xlsm still with the macros, but with table-ized Output sheet and NO Input sheet after macro runs

OR

a. Master.xlsm still with the macros
b. New.xlsx with table-ized Output sheet

[waimea]

Hi Paul,

thank you for your fast reply!

I want to import the input sheet into my master file using the file picker. And then create an output sheet in my master file. I am deleting a lot of the columns but I would like to create more than one copy from the output sheet.

Ideally, 3 copies of the output sheet where I can delete the columns I want to discard from all 3 copies.

I can keep the input sheet in my master file also. Not sure if this answers your question?

[Paul_Hossler]

Starting point ….

Option Explicit

Sub Example2()
    Dim sInputFile As String
    Dim wbInput As Workbook

    'get name
    sInputFile = Application.GetOpenFilename("Input Files (*.xlsx), *.xlsx", , "Open File with Input Worksheet")
    If sInputFile = "False" Then Exit Sub
    Application.ScreenUpdating = False
    
    'delete Input
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Input").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
        
    
    'open Input WB
    Workbooks.Open sInputFile
    Set wbInput = ActiveWorkbook
    wbInput.Worksheets("Input").Copy before:=ThisWorkbook.Worksheets(1)
    wbInput.Close False
    
    ThisWorkbook.Activate
    
    'delete Output, Output2, Output3
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Output").Delete
    Worksheets("Output2").Delete
    Worksheets("Output3").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    
    
    
    '-------------------------------------------------------
    'logic from other macro to create Output goes here
    '-------------------------------------------------------
    Worksheets.Add.Name = "Output"      '   for testing

    
    'make 2 more copies of Output
    Worksheets("Output").Copy after:=Worksheets("Output")
    ActiveSheet.Name = "Output2"
    Worksheets("Output2").Copy after:=Worksheets("Output2")
    ActiveSheet.Name = "Output3"

    Application.ScreenUpdating = False

End Sub

[waimea]

Hi Paul,

this is great, I understand your code and I have learned a lot from your code, especially from the earlier parts with the data transformation.

Thank you very much Paul! I am going to try it at once!

[waimea]

It works really well! Thank you again for your help!

I am marking this thread as solved!