Transposing

[gary]

The cells in Col A contain the following data:

ASMNT
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT
2005
2006
00000
28527
TOTAL
PAID
SITUS
PAREN
ASMNT
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT
2004
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT
2005
2006
00000
TOTAL
PAID
SITUS
PAREN
ASMNT
00000
28527
TOTAL
PAID
SITUS
PAREN
ASMNT
2005
2006
00000
28527
TOTAL
PAID
SITUS
PAREN

How can I "transpose" the cells to look like this
(Note: Each cell from Col A is in a different cell in each row).

ASMNT ASMNT 2005 00000 TOTAL PAID SITUS PAREN
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN
2005
2006
00000
28527
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 2005 00000 TOTAL PAID SITUS PAREN
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 2004 2005 00000 TOTAL PAID SITUS PAREN
2004
2005
00000
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 2005 2006 00000 TOTAL PAID SITUS PAREN
2005
2006
00000
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 00000 28527 TOTAL PAID SITUS PAREN
00000
28527
TOTAL
PAID
SITUS
PAREN
ASMNT ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN
2005
2006
00000
28527
TOTAL
PAID
SITUS
PAREN

(My spreadsheet has 60,000 cells in COL A)

 

[Ragdyer]

Say data starts in A1.

In B1, enter:
=IF(A1="ASMNT",A1,"")

In C1, enter:
=IF(OR(B1={"","PAREN"}),"",INDEX($A:$A,COLUMNS($A:B)+ROWS($1:1)-1))

Copy C1 across to J1.

NOW, select B1 to J1, and copy that *9 cell selection* down as far as
needed.

Depending on whether your numbers are text or real numbers, you may need to
custom format Columns C, D, E, and F to the number of digits you wish to see
displayed.