I have some excel data which are now in rows and I want to get them into columns in an easy an effective way and I am not able to figure out how to do it. Any advice will be welcome! Thanks.

Example: turn sth like this in Excel

Team A
John
Team B
Peter
John
Team C
John
Peter
Oliver
Anna
Team D
Anna

into:

Team A John
Team B Peter John
Team C John Peter Oliver Anna
Team D Anna

I'm guessing your real data is much longer than this list so here is what i would do in that case.

First, put the list in column B and add a formula that copies the Team down column A:

*note you have to copy and paste the value from b2 into a2 and start the formula on a3.
Type the formula =IF(LEFT(B3,4)="Team",B3,A2) in cell a3 and drag it down (or control shift down then control d to fill down). What is this formula doing? It looks at the B cell, if it starts with "Team" it uses the value of that cell, otherwise it uses the value of the cell above (which will be another "Team").

Then, copy and paste and values column A so you don't loose the formula results after the next steps:

Filter column B "player" on the search term "team" and delete those entire rows:

Now you have column A of teams, Column B of players and use this formula in column C: =IF(A2=A1,CONCATENATE(C1," ",B2),CONCATENATE(A2," ",B2)).
This formula looks at the Team column and if it differs, it start a new chain of team and player otherwise it adds the player to the chain above of team and player.

I hope you can follow the logic here and accomplish what you are trying to do. Let me know how it goes.

Column to Vertical List

Option Explicit

'*******************************************************************************
' Purpose: Processes a one-column range containing groups of title-values data,
' transposing the titles to the first column of a range and the values
' to columns next to the title thus creating a vertical list.
'*******************************************************************************

Sub ColumnToVerticalList()

 Const cStrSheet As String = "Sheet1" ' Worksheet Name
 Const cLngFirstRow As Long = 2 ' First Row of Source Data
 Const cStrColumn As String = "A" ' Column of Source Data
 Const cStrSearch As String = "Team" ' Search String
 Const cStrCell As String = "C2" ' Target Cell

 Dim arrSource As Variant ' Source Array
 Dim lngArr As Long ' Source Array Row Counter

 Dim arrTarget As Variant ' Target Array
 Dim lngRows As Long ' Number of Rows (Counter) in Target Array
 Dim iCols As Integer ' Number of Columns (Counter) in Target Array
 Dim iColsTemp As Integer ' Target Array Columns Counter
 Dim strTargetRange As String ' Target Range

 ' Paste the calculated source range into the source array - arrSource.
 With ThisWorkbook.Worksheets(cStrSheet)
 arrSource = .Range( _
 .Cells(cLngFirstRow, cStrColumn), _
 .Cells(.Cells(Rows.Count, cStrColumn).End(xlUp).Row, cStrColumn))
 End With

 ' Calculate the number of rows and columns of the target array - arrTarget.
 iColsTemp = 1
 For lngArr = LBound(arrSource) To UBound(arrSource)
 If InStr(1, arrSource(lngArr, 1), cStrSearch, vbTextCompare) <> 0 Then
 If iColsTemp > iCols Then
 iCols = iColsTemp
 End If
 iColsTemp = 1
 Debug.Print arrSource(lngArr, 1)
 lngRows = lngRows + 1
 Else
 iColsTemp = iColsTemp + 1
 End If
 Next

 ' Calculate the target range address.
 strTargetRange = Range(Cells(Range(cStrCell).Row, Range(cStrCell).Column), _
 Cells(Range(cStrCell).Row + lngRows - 1, _
 Range(cStrCell).Column + iCols - 1)).Address

 ' Resize the target array.
 ReDim arrTarget(1 To lngRows, 1 To iCols)

 ' Write data from source array to target array.
 lngRows = 0
 iCols = 1
 For lngArr = LBound(arrSource) To UBound(arrSource)
 If InStr(1, arrSource(lngArr, 1), cStrSearch, vbTextCompare) <> 0 Then
 iCols = 1
 lngRows = lngRows + 1
 arrTarget(lngRows, 1) = arrSource(lngArr, 1)
 Else
 iCols = iCols + 1
 arrTarget(lngRows, iCols) = arrSource(lngArr, 1)
 End If
 Next

 ' Paste data of the target array into the target range
 ThisWorkbook.Worksheets(cStrSheet).Range(strTargetRange) = arrTarget

End Sub