Hi
SammySpaceman
This was taken from an old submission...you can modifty to fit your needs....Hope this helps....
Posted by Ben W. on November 15, 2001
If you where not opposed to a quick VBA solution you could use the following code. It assumes that the first label "SITE" is in cell A1:
Sub sort_of_transposing_multiple_records()
Dim Current_cell As range
range("A1").EntireRow.Insert
range("A2:A10").Copy
range("A1").PasteSpecial Transpose:=True
range("A2").EntireRow.Insert
Set Current_cell = range("A2")
Do While (Current_cell(2, 1).Value <> "")
For lcv = 1 To 9
Current_cell(1, lcv).Value = Current_cell(2, 2).Value
Current_cell(2, 1).EntireRow.Delete
Next lcv
Set Current_cell = Current_cell(2, 1)
Loop
End Sub
Otherwise I would copy the column you want to transpose 9 times with spaces between the columns. Then auto fill numbers next to it so that each column has one catagory with increasing numbers next to it.
Column 1 would have a 1 next to the first site and a 2 next to the second site and so on. Column 2 would have a 1 next to the first Name and a 2 next to the second name and so on. Then I would sort each column by the numbers next to it and throw away anything below the numbers. After you delete the columns with numbers you will be left 9 columns, each representing one catagory and 700 rows.
pll
