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

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!