Transpose Array to Single Column

Hi Folks,

I'm trying to transpose an array of data into a single column. The array will eventually be around 30 columns wide by thousands of rows long, unlike the example that is currently set at 10x15. The formula I am looking for needs to list the row data in a columnar format as shown in column S and not olny that, it's needs to reference the respective ID number from Column A (see column T). The colour coding I have set up is pretty self explanatory.

Thanks in advance.

transpose2.xlsx

With the code below you can transpose the data, like you showed.

There where no data in row 1 from column H and further, so I added an formula.

It does not split the values of (e.g.) cell c6 into column (first).

Edit:

To split the data in column C, you could probably use text to column in excel 2007 and use ; (semicolon) as seperator.
Since I don't have excel 2007 available on the moment I can test that for you.

See the attached file.

Sub CONVERTROWSTOCOL_Oeldere_revisted()

Dim rsht1 As Long, rsht2 As Long, i As Long, Col As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

   
   With Sheets("Output")
    .UsedRange.ClearContents
    .Range("A1:B1").Value = Array("Name", "Project")

    End With
    
    
    rsht1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    Col = 9
    
    For i = 4 To rsht1
            Do While Sheets("sheet1").Cells(1, Col).Value <> ""
            rsht2 = rsht2 + 1
           
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet1").Range("A" & i).Value
                
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet1").Cells(i, Col).Value
            
            
     
            Col = Col + 1
        Loop
        Col = 9
    Next
  With Sheets("Output")
   .Range("A2:A" & .Rows.Count).SpecialCells(4).EntireRow.Delete
   Columns("A:Z").EntireColumn.AutoFit
    End With
End Sub

Thanks for that oeldere, it seems to work very well. I did use the text to column function to extract the data in columns I:P.

Is there a way to do this without using VBA though, the people who will eventually use this document are better with formulas than coding?

Thanks once again.

You can use this formula. In I4 and pull it to the right until you see blanks.

=TRIM(MID(SUBSTITUTE($B4,";",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Nice formula Alkey! I'll definitely use that one as an alternative to "Text to Columns". Unfortulately my problem lies in transposing the data into a single column (see column S and T). I'm hoping there is a formula solution that can do exactly the same as what oeldere provided in his VBA suggestion.

Oh, boy! Sorry, I misread your requirements. Unfortunately, the only way to get it down would be with VBA. The solution offered by oeldere is exactly what you need.

Seems like I have to study up on VBA coding now . Thank you both for your assitance in this regard.

If you wouldn't mind, could you break down the VBA code for me so that I can get an idea of what it's actually doing. As mentioned in my first post the data I will be dealing with will be considerably larger and I would like to be able to customise the VBA code accordingly (i.e. change search range). I just tried adding a few extra pieces of data to the sample and the OutPut tab remained the same. Is there something I need to do to 'initiate/activate' the code so that it continually updates the OutPut tab?

Sub CONVERTROWSTOCOL_Oeldere_revisted()

Dim rsht1 As Long, rsht2 As Long, i As Long, Col As Long, wsTest As Worksheet

general setting to the code.

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

code for making (and checking if the sheet Output exist) a new worksheet output

  With Sheets("Output")
    .UsedRange.ClearContents                                                 'delete all data on the sheet output 
    .Range("A1:B1").Value = Array("Name", "Project")              'make a header with the names Name Project

    End With

  rsht1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row   'start counting the rows in sheet 1 from the last row in column A to 1
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row  'start counting the rows in sheet output from the last row in column A to 1
    Col = 9                                                                                         'begin with the values in column 9

  For i = 4 To rsht1                                                                          'from last row in sheet 1, column A till row 4
            Do While Sheets("sheet1").Cells(1, Col).Value <> ""                'use all columns in row 1 in which the cell contents is <> "" (are not blanc)
            rsht2 = rsht2 + 1                                                                  'add 1 row in sheet output 
           
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet1").Range("A" & i).Value  'copy to sheet output, column B to the last row
                                                                                                                                        '(from) take the values in column A   
                
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet1").Cells(i, Col).Value      'copy to sheet output, column A to the last row
                                                                                                                                         '(from) take the values in all cells (rows) from the give column (in this case 9)  

            
            
     
            Col = Col + 1                                                                                                       'go to the next column
        Loop
        Col = 9
    Next
  With Sheets("Output")                                                                                                    'only in the sheet Output      
   .Range("A2:A" & .Rows.Count).SpecialCells(4).EntireRow.Delete                                       'delete the empty values in column A
   Columns("A:Z").EntireColumn.AutoFit                                                                             'format the cells with for all columns in the range A till Z
    End With
End Sub

Hope I explained well enough.

Please reply.

Since you posted this in the Formula & Functions forum, I presume you were originally hoping for a purely formula-based solution?

That can be done, and is not overly complex, though with the size of your dataset it would certainly be best achieved using VBA, as you have been given.

However, if you absolutely insist on a formula-based solution (and I would't necessarily recommend it for the size of the dataset that you have) then let me know and I will take you through the steps.

Regards

Thank you oeldere for the step by step guide, it does make a lot more sense now and I will have to play around with it some more to really understand it's working. A few questions though:

1. Should the Output tab update automatically every time data is changed in the source tab?
2. If I were to assign a macro to a text box, would this run the vba code that you set up and generate the Output tab?

I guess what I'm trying to figure out is, if the Output tab doesn't update automatically, how would I generate a new list every time the source data changes.

Thanks to all for your help so far.

I would be interested XOR LX, thank you. As you say I will probably have to use the VBA for this exercise, but I would definitely like to know how to do it for smaller data sets.

Originally Posted by jeversf

I would be interested XOR LX, thank you. As you say I will probably have to use the VBA for this exercise, but I would definitely like to know how to do it for smaller data sets.

Perhaps have a look at the technique outlined here and see if you can adapt it to your needs, should you wish to:

http://excelxor.com/2014/08/24/singl...-1-rows-first/

If you need any help with it let me know.

Regards

1) only if you run the macro again.

2) ?