transforming column data to rows on a new worksheet

[dbsa]

Hi
I have a spreadsheet as follows:
Col A: phone number
Col B: text msg.

So data might look like:
Col A.......Col B
99998888 Good Morning Robert
77776666 Greetings Mary

I want to create a single-column text file which would present the above data as:
99998888
Good Morning Robert
77776666
Greetings Mary

So I'm looking at creating a second Worksheet with formulas in cells to copy across the data into a single column for saving as text.

Any help would be most appreciated,
James

 

[Sandeep Warrier]

Hi,

Try... (drag down the formula in E1 as required)

Excel Workbook
	A	B	C	D	E
1	8887	Good Morning Robert			8887
2	81874	Greetings Mary			Good Morning Robert
3	33847	Hello Bob			81874
4	51722	G'Morning Katy			Greetings Mary
5	99195	Good Morning Robert			33847
6	57419	Greetings Mary			Hello Bob
7	98331	Hello Bob			51722
8	17369	G'Morning Katy			G'Morning Katy
9	23433	Good Morning Robert			99195
10	6774	Greetings Mary			Good Morning Robert
11	52728	Hello Bob			57419
12	90982	G'Morning Katy			Greetings Mary
13	8172	Good Morning Robert			98331
14	99338	Greetings Mary			Hello Bob
15	30258	Hello Bob			17369
16	89581	G'Morning Katy			G'Morning Katy
17	79542	Good Morning Robert			23433
18	37985	Greetings Mary			Good Morning Robert
19	78549	Hello Bob			6774
20	32893	G'Morning Katy			Greetings Mary
21	16555	Good Morning Robert			52728
22	73242	Greetings Mary			Hello Bob
23	66508	Hello Bob			90982
24	3344	G'Morning Katy			G'Morning Katy
25	86641	Good Morning Robert			8172
26	25042	Greetings Mary			Good Morning Robert
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.


Alternatively, you could use a simple macro to do the same.

Sub TabletoCol()
    Dim LR As Long, Rng As Range, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Set Rng = Range(Range("A" & i), Range("A" & i).End(xlToRight))
        If Range("E1").Value = "" Then
            Range("E1").Resize(Rng.Count).Value = Application.WorksheetFunction.Transpose(Rng)
        Else
            Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Resize(Rng.Count).Value = Application.WorksheetFunction.Transpose(Rng)
        End If
    Next i
End Sub

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">