Can this be done?

  • Thread starter Thread starter webguy262
  • Start date Start date
W

webguy262

I am a very basic excel user. I have a sheet which contains a few
columns of data specific to a coach, followed by a few columns of data
about each player on that coach's team.

The player columns are, say, player_1_name, player_1_number,
player_1_position, player_2_name, player_2_number, player_2_position,
etc. up to 15 players.

What I need is for the the columns of player data to line up in rows
so all the player names are in the same column, all the player
numbers are in the same column, etc.

The coach columns for these additional rows can either be blank or be
repeated for each additional row.

Can this be done?

Thanks in advance...
 
with the information given, the options are to manually select each set of 3
cells and copy and paste them underneath the 1st player data as you insert
rows.

If you want someone to write code that does that for you you would have to
spell out which column player_1_name is in

which row is the first coach listed on

is the data currently with one coach/team per row with no separation? If
not, how is it laid out. How many teams.

What is the name of the sheet?
 
Tom

Thanks for replying!

First Coach is on row 2...each row has a coach...no empty rows
between.

There are actually many more than three columns for coach and player
data.

The Coach data is in cols A-H.
Data for 15 Players is in cols I-U, V-AH, AI-AU, AV-BH, BI-BU, BV-CH,
CI-CU, CV-DH, DI-DU, DV-EH, EI-EU, EV-FH, FI-FU, FV-GH, GI-GU.

203 teams (204 rows w/col headers)

Sheet is named 'rosters'

Can you work some magic with this info?

Barry

PS. Let me know if having a sheet with the col headers in it will
help. I'll email it to you.

Here are the actual col heads (customer = coach) without the col
positions:

customers_firstname customers_lastname customers_email_address
customers_telephone customers_fax customers_aau_team_name
customers_aau_team_age customers_coach_cell customers_p1_number
customers_p1_fname customers_p1_lname customers_p1_court_position
customers_p1_height_feet customers_p1_height_inches
customers_p1_address customers_p1_city customers_p1_state
customers_p1_zip customers_p1_phone customers_p1_email
customers_p1_grad_year customers_p2_number customers_p2_fname
customers_p2_lname customers_p2_court_position
customers_p2_height_feet customers_p2_height_inches
customers_p2_address customers_p2_city customers_p2_state
customers_p2_zip customers_p2_phone customers_p2_email
customers_p2_grad_year customers_p3_number customers_p3_fname
customers_p3_lname customers_p3_court_position
customers_p3_height_feet customers_p3_height_inches
customers_p3_address customers_p3_city customers_p3_state
customers_p3_zip customers_p3_phone customers_p3_email
customers_p3_grad_year customers_p4_number customers_p4_fname
customers_p4_lname customers_p4_court_position
customers_p4_height_feet customers_p4_height_inches
customers_p4_address customers_p4_city customers_p4_state
customers_p4_zip customers_p4_phone customers_p4_email
customers_p4_grad_year customers_p5_number customers_p5_fname
customers_p5_lname customers_p5_court_position
customers_p5_height_feet customers_p5_height_inches
customers_p5_address customers_p5_city customers_p5_state
customers_p5_zip customers_p5_phone customers_p5_email
customers_p5_grad_year customers_p6_number customers_p6_fname
customers_p6_lname customers_p6_court_position
customers_p6_height_feet customers_p6_height_inches
customers_p6_address customers_p6_city customers_p6_state
customers_p6_zip customers_p6_phone customers_p6_email
customers_p6_grad_year customers_p7_number customers_p7_fname
customers_p7_lname customers_p7_court_position
customers_p7_height_feet customers_p7_height_inches
customers_p7_address customers_p7_city customers_p7_state
customers_p7_zip customers_p7_phone customers_p7_email
customers_p7_grad_year customers_p8_number customers_p8_fname
customers_p8_lname customers_p8_court_position
customers_p8_height_feet customers_p8_height_inches
customers_p8_address customers_p8_city customers_p8_state
customers_p8_zip customers_p8_phone customers_p8_email
customers_p8_grad_year customers_p9_number customers_p9_fname
customers_p9_lname customers_p9_court_position
customers_p9_height_feet customers_p9_height_inches
customers_p9_address customers_p9_city customers_p9_state
customers_p9_zip customers_p9_phone customers_p9_email
customers_p9_grad_year customers_p10_number customers_p10_fname
customers_p10_lname customers_p10_court_position
customers_p10_height_feet customers_p10_height_inches
customers_p10_address customers_p10_city customers_p10_state
customers_p10_zip customers_p10_phone customers_p10_email
customers_p10_grad_year customers_p11_number customers_p11_fname
customers_p11_lname customers_p11_court_position
customers_p11_height_feet customers_p11_height_inches
customers_p11_address customers_p11_city customers_p11_state
customers_p11_zip customers_p11_phone customers_p11_email
customers_p11_grad_year customers_p12_number customers_p12_fname
customers_p12_lname customers_p12_court_position
customers_p12_height_feet customers_p12_height_inches
customers_p12_address customers_p12_city customers_p12_state
customers_p12_zip customers_p12_phone customers_p12_email
customers_p12_grad_year customers_p13_number customers_p13_fname
customers_p13_lname customers_p13_court_position
customers_p13_height_feet customers_p13_height_inches
customers_p13_address customers_p13_city customers_p13_state
customers_p13_zip customers_p13_phone customers_p13_email
customers_p13_grad_year customers_p14_number customers_p14_fname
customers_p14_lname customers_p14_court_position
customers_p14_height_feet customers_p14_height_inches
customers_p14_address customers_p14_city customers_p14_state
customers_p14_zip customers_p14_phone customers_p14_email
customers_p14_grad_year customers_p15_number customers_p15_fname
customers_p15_lname customers_p15_court_position
customers_p15_height_feet customers_p15_height_inches
customers_p15_address customers_p15_city customers_p15_state
customers_p15_zip customers_p15_phone customers_p15_email
customers_p15_grad_year
 
Sub Tester2()
Dim rng As Range
Dim sh1 As Worksheet
Dim sh As Worksheet
Dim cell As Range
Set sh = ActiveSheet
Set rng = Range(Cells(2, "A"), _
Cells(2, "A").End(xlDown))
Set sh1 = Worksheets.Add(after:= _
Worksheets(Worksheets.Count))
sh1.Cells.ClearContents
sh.Range("A1").Resize(1, 21).Copy sh1.Range("A1")
rw = 2
For Each cell In rng
cell.Resize(1, 8).Copy sh1.Cells(rw, 1)
For i = 9 To 203 Step 13
If cell.Offset(0, i - 1).Value <> "" Then
cell.Offset(0, i - 1).Resize(1, 13) _
.Copy sh1.Cells(rw, 9)
rw = rw + 1
End If
Next
rw = rw + 1
Next
End Sub

Make your data sheet the activesheet. Then run the macro. It adds a new
sheet and places the reformatted data there.
 
Tom

Perfect!

Barry

Sub Tester2()
Dim rng As Range
Dim sh1 As Worksheet
Dim sh As Worksheet
Dim cell As Range
Set sh = ActiveSheet
Set rng = Range(Cells(2, "A"), _
Cells(2, "A").End(xlDown))
Set sh1 = Worksheets.Add(after:= _
Worksheets(Worksheets.Count))
sh1.Cells.ClearContents
sh.Range("A1").Resize(1, 21).Copy sh1.Range("A1")
rw = 2
For Each cell In rng
cell.Resize(1, 8).Copy sh1.Cells(rw, 1)
For i = 9 To 203 Step 13
If cell.Offset(0, i - 1).Value <> "" Then
cell.Offset(0, i - 1).Resize(1, 13) _
.Copy sh1.Cells(rw, 9)
rw = rw + 1
End If
Next
rw = rw + 1
Next
End Sub

Make your data sheet the activesheet. Then run the macro. It adds a new
sheet and places the reformatted data there.
Click to expand...
 
Back
Top