Transposing/Formatting Data

[DShack]

I have a list of data (see below) that has different position/depts/rates that i need to transpose into another excel sheet in a different format (Horizontal) instread of veritcally.

Data removed


Need the above data to be in the below format: I added this in manually show how i need the data to look

Data removed

 

[pbornemeier]

This code will convert the single column A data you have to multiple columns (B:H). I made some asssumptions on the data layout, so be sure to check that the output is valid for each row and make the appropriate changes to the code it it is not.

Option Explicit

Sub ConvertWorksheet()
    Dim varLineData As Variant
    Dim lX As Long
    
    Range("B1").Resize(1, 7).Value = Array("Emp ID", "Name", "Posn Order", "Department", "Position", "Old rate", "New Rate")
    
    For lX = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        varLineData = ConvertOneRow(Cells(lX, 1).Value)
        Cells(lX, 2).Resize(1, UBound(varLineData) + 1).Value = varLineData
    Next
    
End Sub

Function ConvertOneRow(sInput As String)

    Dim sID As String
    Dim sName As String
    Dim sPosOrder As String
    Dim sDept As String
    Dim sPosition As String
    Dim curOldRate As Currency
    Dim curNewRate As Currency
    
    Dim lDashPos As Long
    Dim lOpenParaPos As Long
    Dim lCloseParaPos As Long
    Dim lSpacePos As Long
    Dim lTextPos As Long
    Dim lTextLen As Long
    
    sInput = Trim(sInput)
    lSpacePos = InStr(sInput, " ")
    sID = Left(sInput, lSpacePos - 1)
    sInput = Mid(sInput, lSpacePos + 3)
    
    lOpenParaPos = InStr(sInput, "(")
    sName = Trim(Left(sInput, lOpenParaPos - 1))
    lCloseParaPos = InStr(sInput, ")")
    sInput = Trim(Mid(sInput, lCloseParaPos + 1))
    
    lSpacePos = InStr(sInput, " ")
    sPosOrder = Left(sInput, lSpacePos - 1)
    sInput = Mid(sInput, lSpacePos + 1)
    
    lDashPos = InStr(12, sInput, "-")
    sDept = Trim(Left(sInput, lDashPos - 10))
    lTextLen = Len(sDept)
    sInput = Trim(Mid(sInput, lTextLen + 2))

    lSpacePos = InStrRev(sInput, " ")
    curNewRate = Trim(Mid(sInput, lSpacePos + 1))
    sInput = Trim(Left(sInput, lSpacePos - 1))

    lSpacePos = InStrRev(sInput, " ")
    curOldRate = Trim(Mid(sInput, lSpacePos + 1))
    sPosition = Trim(Left(sInput, lSpacePos - 1))
    
    Convert = Array(sID, sName, sPosOrder, sDept, sPosition, curOldRate, curNewRate)
    
End Function

Another step will be needed to put the data in the horizontal format that you described.

The first person shows position numbers 2 to 7, but the final example shows what was postion 2 in position 1. Is this a desired behavior?
What is the maximum number of positions possible?
What goes in the percent columns?