Solved Move row values to new columns? (1 Viewer)

[hfl]

Hello,

Pretty new to coding in Excel so hoping for some help in here

We got a many .csv log files as under but with +10k rows and 88 different tagnames, I have manually deleted 85 of them to make the example in this post smaller.

So what I hope to automate is that it looks like the table under where Date, Time, Millitm is combined and Tagname with the values goes into their own column.

I have attached our solution to the problem but if anyone got a better way I am all ears

;Date,Time,Millitm,Tagname,Value,Status,Marker,Internal
04/03/2020,13:12:59,527,/RSLINXENTERPRISE::[EDG]BATTSTARTAVOLTAGE.Y                                                                                                                                                                                                                    ,       0.00000000,U,B,        -1,
04/03/2020,13:12:59,527,/RSLINXENTERPRISE::[EDG]BATTISNTAVOLTAGE.Y                                                                                                                                                                                                                     ,       0.00000000,U,B,        -1,
04/03/2020,13:12:59,527,/RSLINXENTERPRISE::[EDG]FREQUENCYTOEDMCS.Y                                                                                                                                                                                                                     ,       0.00000000,U,B,        -1,
04/03/2020,13:12:59,527,/RSLINXENTERPRISE::[EDG]BATTSTARTACURRENT.Y                                                                                                                                                                                                                    ,       0.00000000,U,B,        -1,
04/03/2020,13:13:08,416,/RSLINXENTERPRISE::[EDG]BATTSTARTAVOLTAGE.Y                                                                                                                                                                                                                    ,      27.38437653, ,E,         0,
04/03/2020,13:13:08,416,/RSLINXENTERPRISE::[EDG]BATTISNTAVOLTAGE.Y                                                                                                                                                                                                                     ,      26.08875084, ,E,         1,
04/03/2020,13:13:08,416,/RSLINXENTERPRISE::[EDG]FREQUENCYTOEDMCS.Y                                                                                                                                                                                                                              
04/06/2020,17:17:12,496,/RSLINXENTERPRISE::[EDG]BATTSTARTAVOLTAGE.Y                                                                                                                                                                                                                    ,       0.00000000,U,B,        88,
04/06/2020,17:17:12,496,/RSLINXENTERPRISE::[EDG]BATTISNTAVOLTAGE.Y                                                                                                                                                                                                                     ,       0.00000000,U,B,        89,
04/06/2020,17:17:12,496,/RSLINXENTERPRISE::[EDG]FREQUENCYTOEDMCS.Y                                                                                                                                                                                                                     ,       0.00000000,U,B,        90,
04/06/2020,17:18:12,460,/RSLINXENTERPRISE::[EDG]BATTSTARTAVOLTAGE.Y                                                                                                                                                                                                                    ,      19.51312637, , ,       176,
04/06/2020,17:18:12,460,/RSLINXENTERPRISE::[EDG]BATTISNTAVOLTAGE.Y                                                                                                                                                                                                                     ,      28.13625145, , ,       177,
04/06/2020,17:18:12,460,/RSLINXENTERPRISE::[EDG]FREQUENCYTOEDMCS.Y                                                                                                                                                                                                                     ,       0.00000000, , ,       178,

Date	Time	Millitm	BATTSTARTAVOLTAGE.Y	BATTISNTAVOLTAGE.Y	FREQUENCYTOEDMCS.Y
04.03.2020​	13:12:59​	527​	0​	0​	0​
04.03.2020​	13:13:08​	416​	27,38437653​	26,08875084​	0​
04.06.2020​	17:17:12​	496​	0​	0​	0​
04.06.2020​	17:18:12​	460​	19,51312637​	28,13625145​	0​
04.06.2020​	17:19:12​	534​	26,1243763​	28,07437706​	0,12001999​

Any help is appreciated

 

[arnelgp]

maybe Pivot table can do it.

 

[namliam]

maybe Pivot table can do it.

No maybe about it, this is a pivot table.... though 88 columns is somthing of a pivot table.

 

[hfl]

Thanks for the suggestion but got help by a colleague

In case someone should have a similar problem here is the working code.

Option Explicit

Sub Convert_and_format_test_results()

    'Always open this selected sheet
    ActiveWorkbook.Sheets("TestResultsImported").Activate

    'Clear Sheets
    Sheets("TestResultsImported").UsedRange.ClearContents
    Sheets("TestResultsConverted").Delete
    Sheets("TestResultsFormatted").Delete

    'Get name of first sheet
    Dim strSheetName As String
  
    strSheetName = ActiveSheet.Name
  
    'Add new sheet for storing converted test results
    Sheets(strSheetName).Activate
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add(After:=ActiveSheet).Name = "TestResultsConverted"
    ActiveSheet.Paste
  
    'Delimit data
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

    'Add sheet to store formatted test results
    Sheets.Add(After:=ActiveSheet).Name = "TestResultsFormatted"
    
    'Set up column headers
    Range("A1").Value = "Date"
    Range("B1").Value = "Time"
    Range("C1").Value = "Millitm"
  
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim row_num
  
    'Find number of unique timestamps
    Sheets("TestResultsConverted").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 0).Range("A1").Select
    row_num = ActiveCell.Row
  
    '88 = number of lines before logging start over
    k = row_num / 88
  
    Sheets("TestResultsFormatted").Select
  
    'Paste tag names in column headers - 88 = number of tag names
    For i = 1 To 88
        Cells(1, 3 + i) = Sheets("TestResultsConverted").Cells(i, 4).Value
    Next
  
    'Paste values
    For j = 0 To k
        For i = 1 To 88
            Cells(2 + j, 1) = Sheets("TestResultsConverted").Cells(1 + (j * 88), 1).Value
            Cells(2 + j, 2) = Sheets("TestResultsConverted").Cells(1 + (j * 88), 2).Value
            Cells(2 + j, 3) = Sheets("TestResultsConverted").Cells(1 + (j * 88), 3).Value
            Cells(2 + j, 3 + i) = Sheets("TestResultsConverted").Cells(i + (j * 88), 5).Value
        Next
    Next
      
    'Format column B to display time
    Sheets("TestResultsFormatted").Range("B:B").NumberFormat = "[$-x-systime]h:mm:ss AM/PM"

End Sub

 

[namliam]

A lot of stuff going on there, only a small part about what you are talking about.
Seems like a long and wrong way to get from A to B by way of E, but as long as you have a working solution good for you

 

[hfl]

Have updated my first post to the .csv file instead of the table I got after my first import and updated the solution we found to only include the code for my actual problem

But if anyone have a shorter way that would be super and I am always up for improving the code