help urgent

D

daroc

hey guys

hows it going? I need your help once again....i have an excel file wit
loads of data

ie.

A ....G H I J
K L
1 10465 Jason Brad Mike Jeff
Heather Lebron

2 1076 Keith Jay Fred David
Young Pat

and it goes all the way down to A4056.....however on a seperate sheet
need this information copied like this

SHEET 2

A B
1 10465 Jason
Brad
Mike
Jeff
Heather
Lebron

2 1076 Keith
Jay
Fred
David
Young
Pat

once again, it goes all the way down to A4056. is there a way eithe
thru macro or thru formula for me to do this instead of copying an
pasting special? help will be much appreciated; please and thank you
hope you follow what I was trying to get at....
 
D

Dave Peterson

How about a macro?

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim RngToCopy As Range

Set curWks = Worksheets("Sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 2 'headers in row 1????
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
Set RngToCopy _
= .Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
RngToCopy.Copy
newWks.Cells(oRow, "B").PasteSpecial Transpose:=True
oRow = oRow + RngToCopy.Cells.Count
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

daroc

I am having trouble running the macro....if i send you a sample of wha
i am doing on excel....can you pls/kindly make the macro for me...an
ill learn it from that and implement it to the rest? let me kno
pls...ill attach the fil
 
Top