HELP-Rearranging data into columns

[S4squ4tch]

Good Morning All,

So I have a lot of data in rows that I need to rearrange and organize into columns. Transpose doesn't quite fulfill my needs and was hoping to find a way to write a formula that will capture what I am trying to do. I will include an example of what I am looking for below.


So here is what I have right now:

ID#11111	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11111	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11112	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11112	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11113	Product Name	Manufacturer Name	Supplier Name	Distributor Name
ID#11113	Product Name	Manufacturer Name	Supplier Name	Distributor Name

<tbody>
</tbody>

What I am trying to do

Product Name	ID#11111
Manufacturer Name
Supplier Name
Distributor Name
Product Name	ID#11112
Manufacturer Name
Supplier Name
Distributor Name
Product Name	ID#11113
Manufacturer Name
Supplier Name
Distributor Name

<tbody>
</tbody>

Any and all help and suggestions would be GREATLY appreciated!

Thanks, all!

 

[S4squ4tch]

Thanks, Rick. I was just trying to understand the correct thought process to complete what I was striving for. I am just interested in learning new thought processes to solve problems. My apologies if this caused an issue for you.

Thanks again!

 

[SpillerBD]

Its sort of an Unpivot, which Power Query can take care of the biggest part of the transformation...
What version of Excel do you have and have you tried Power Query before?
See link for more info on Power Query. Download Microsoft Power Query for Excel from Official Microsoft Download Center

 

[S4squ4tch]

Hey SpillerBD,

I am using Excel 2013. Never used Power Query before. Ill take a look. Thanks for your response and any additional help!

 

[SpillerBD]

I was bored. I know Rick probably would have a better way to loop through your data using arrays, but this should speed things up for you.
Remember run o a copy of your source data for testing

Sub ManIDTranspose() ' Loop through each row
    
    Dim DestinationRow As Integer
    Dim SourceSheet As String
       
    Dim T_ID As String
    Dim T_Name As String
    Dim T_E_Name As String
    Dim T_E_ID As String
    Dim TC1 As String
    Dim TC2 As String
    Dim tc3 As String
    Dim TMID As String
    Dim TMName As String
    
    DestinationRow = 1
 
SourceSheet = ActiveSheet.Name 'Run Macro with ource sheet as active sheet
findname = "Data Transposed" 'Name usd for destination
On Error Resume Next 'skip the error if the name doesn't exist
    myName = ActiveWorkbook.Worksheets(findname).Name
    If Err.Number = 0 Then
    NameExists = True
        Else
    NameExists = False
        Worksheets.Add.Name = findname 'If sheet didn't exist it is created
    End If

With Worksheets(findname)
    .Cells.Clear 'Destination sheet is cleared of all data
    .Range("A1").Value = "ID"
    .Range("B1").Value = "ID NAME"
    .Range("C1").Value = "EMPLOYEE NAME"
    .Range("D1").Value = "EMPLOYEE ID"
    .Range("E1").Value = "CODE 1"
    .Range("F1").Value = "CODE 2"
    .Range("G1").Value = "CODE 3"
    
    .Range("H1").Value = "MANAGER ID"
    .Range("I1").Value = "MANAGER NAME" 'Resets Destination header
End With
FinalRow = Worksheets(SourceSheet).Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To FinalRow + 1
    T_ID = Worksheets(SourceSheet).Cells(i, 1).Value
    T_Name = Worksheets(SourceSheet).Cells(i, 2).Value
    T_E_Name = Worksheets(SourceSheet).Cells(i, 3).Value
    T_E_ID = Worksheets(SourceSheet).Cells(i, 4).Value
    TC1 = Worksheets(SourceSheet).Cells(i, 5).Value
    TC2 = Worksheets(SourceSheet).Cells(i, 6).Value
    tc3 = Worksheets(SourceSheet).Cells(i, 7).Value
    
    For j = 8 To 16 Step 2  'Loops thru manager blocks
        DestinationRow = DestinationRow + 1 'Destination Counter/advance
        TMID = Worksheets(SourceSheet).Cells(i, j).Value
        TMName = Worksheets(SourceSheet).Cells(i, j + 1).Value
        
        With Worksheets(findname)
            .Cells(DestinationRow, 1).Value = T_ID
            .Cells(DestinationRow, 2).Value = T_Name
            .Cells(DestinationRow, 3).Value = T_E_Name
            .Cells(DestinationRow, 4).Value = T_E_ID
            .Cells(DestinationRow, 5).Value = TC1
            .Cells(DestinationRow, 6).Value = TC2
            .Cells(DestinationRow, 7).Value = tc3
            
            .Cells(DestinationRow, 8).Value = TMID
            .Cells(DestinationRow, 9).Value = TMName
        
        End With
        
    Next j
Next i

End Sub