Transposing/Formatting Data

DShack

Board Regular
Joined
Jan 15, 2014
Messages
55
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
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.
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,274
Members
414,050
Latest member
Rick Royer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top