Transposing Excel Data

[Brianfree]

Hi, I have exported some customer data from sage accounts into excel but I need to transpose the data so I can import it into another application.

This is how the data looks in excel

A/C	Name & Address
ABC123	Company Name 1
	Address 1
	Address 2
	Address 3
	Address 4
	Address 5
DEF456	Company Name 2
	Address 1
	Address 2
	Address 3
	Address 4
	
GHI789	Company Name 3
	Address 1
	Address 2
	Address 3
	
	Address 5

This is how I would like it to look..

A/C	Name & Address	Col1	         Col2	         Col3	         Col4	         Col5
ABC123	Company Name 1	Address 1	Address 2	Address 3	Address 4	Address 5
DEF456	Company Name 2	Address 1	Address 2	Address 3	Address 4	
GHI789	Company Name 2	Address 1	Address 2	Address 3		         Address 5

Please can anyone advise or is there a macro do do this?

Many thanks

Brian ;-)

 

[Brianfree]

I have found a formula which works fine!

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Hope this can help someone else!

Cheers

Brian