Need help re-arranging data within a file automatically

[stubs]

Please can anyone help me?
I have a file containing data about 300+ people. Each person has 3 fields of data and the worksheet is currently laid out like this:

1. User name
2. Full Name
3. Comment
4.
5.
6. User name
7. Full Name
8. Comment
9.
10.
.....

...in column A, with the respective data in column B.[/i]


I would like the data to be re-arranged so that column A contains all the "User name" data, column B contains the "Full Name" data, and column C contains the "Comment" data, with each row reprsenting a different person.. i.e.

1. Alessio Lobascio | EDAL2282 | FM Chivasso
2. Alberto Sismondi | EDAS2312 | FM Chivasso
3. Diego Sandiano | EDDS2465 | FM Chivasso
4. Fiorella D'Appollonio | EDFD1267 | FM Chivasso
....

Is there a quick way of doing this, possibly with a Macro, or some rule?..
Cheers,
Stu

 

[pgc01]

Hi Stu

Whith the layout in the example, write in D2:

=INDEX($B:$B,5*(ROWS($B$1:D2)-2)+COLUMNS($D2:D2))

Copy accross to E:F and down

Then copy, paste special values D:F and delete A:C.

Hope this helps
PGC

Book1
	A	B	C	D	E	F	G
1	UserName	SmithK		UserName	Fullname	Comments
2	Fullname	KarenSmith		SmithK	KarenSmith	User
3	Comments	User		DoeJ	JohnDoe	Guest
4				WellsF	FrankWells	Administrator
5				WhiteM	MaryWhite	User
6	UserName	DoeJ
7	Fullname	JohnDoe
8	Comments	Guest
9
10
11	UserName	WellsF
12	Fullname	FrankWells
13	Comments	Administrator
14
15
16	UserName	WhiteM
17	Fullname	MaryWhite
18	Comments	User
19
Sheet1

 

[stubs]

Thankyou SO much for your reply.. it worked a treat, and I have learnt from it also.


Cheers!