Help me with this!

Hello, here is the code, hope it helps. i've added a lot of commentary in the code itself. the first step you need to do is to copy your second table from above, then paste it to cell A11. After pasting it to cell A11, run the macro below. your example in table1 does not exactly match your data in table2, so it is kind of hard to determine your desired outcome. cheers!

Sub Macro1()
'''you need to copy and paste your sample data to cell A11

'''go to A11
Application.Goto Reference:="R11C1"
''copy cells A11 to XFC16. so, it copies 6 rows. change 6 to 64 or however many rows you have. so, XFC6 becomes XFC64
ActiveCell.Range("A1:XFC6").Select
Selection.Copy


'insert a new sheet, go to A11, paste as transpose
Sheets.Add
Application.Goto Reference:="R11C1"
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

''your data is pretty messy, it looks like the next step you want is to parse column A.
''instead of parsing column A, let's copy A to AA, so that your original data stays exactly as it was
''if you need to delete columns or move columns later, you can do it manually.

'go to A1, copy to AA1
Application.Goto Reference:="R1C1"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Application.Goto Reference:="R1C27"
ActiveSheet.Paste
Application.Goto Reference:="R1C27"

''add the letters parse_here in cell aa1 because when the first row is blank during a parsing operation,
'' Excel can sometimes shift the data up one row. not sure why that is, but i've seen it too many times.

Selection.FormulaR1C1 = "'parse_here"

'parse by underscore delimiter
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

''now, it looks like you also need to parse column AC, as it contains a hypen

'add in dummy word parse_here in AC1
Application.Goto Reference:="R1C29"
Selection.FormulaR1C1 = "parse_here"

'''parse by hypen as the delimiter
Application.Goto Reference:="R1C29"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

''select AA to AP and best fit columns
Application.Goto Reference:="R1C27"
ActiveCell.Columns("A:P").EntireColumn.Select
Selection.Columns.AutoFit
End Sub