How can I change the data in columns into headers?

In an excel worksheet, I have repeated data in column 1. Now I want to sort
it and make the data in column 1 the headers. Difficult to explain, but if I
can send you my excel sheet you will understand it right away.....
Hope somebody can help.....
Jan Wind

If you want to take the information in Column A (columns have letters, rows
have numbers) and make it the headers for information in other columns by
placing the entries from the sorted column A across row 1 (or any other row,
really), then once you have it sorted, copy it and choose where to start it
as headers and use
Edit | Paste Special and check the box next to [Transpose]. Works fine if
just a one-time deal or a not very often performed action.

A more automatic way would be to put formulas in row 1. This assumes that
you want automatic column names beginning at B1 and going to the right from
there and that they are based on Column A entries beginning at row 2 - the
top of your sorted list. Put this formula in B1 and extend it to the right
=OFFSET($A$1,COLUMN(A1),0)



"Jan" wrote:

> In an excel worksheet, I have repeated data in column 1. Now I want to sort
> it and make the data in column 1 the headers. Difficult to explain, but if I
> can send you my excel sheet you will understand it right away.....
> Hope somebody can help.....
> Jan Wind

Somehow I don't think what I recommended is going to work? Upon reflection
you say that the information in the column is repeated, and that means that
there are going to be duplicate entries grouped together. Which means many
entries in row 1 are going to be the same also.

Ok, I'll bite - send the workbook to 2kmaro @ dslr.net (remove spaces) and
maybe some more explanation and I'll work up some code to do the job. Expect
a day or two delay on response. Maybe someone will come up with already
written code or wild function to do the job before then.

"Jan" wrote:

> In an excel worksheet, I have repeated data in column 1. Now I want to sort
> it and make the data in column 1 the headers. Difficult to explain, but if I
> can send you my excel sheet you will understand it right away.....
> Hope somebody can help.....
> Jan Wind

Sorry, had a brain-phhhhtt! I believe you can do it with this code.

Sub MakeHeaders()
'assumes on proper sheet when you start
'set up for sorted data starting at A2
'headers to start at B1
Const FirstDataItem = "A2" ' change as needed
Const FirstHeaderEntry = "B1" ' change as needed
Dim LastHeader As String
Dim RowOffset As Integer
Dim ColumnOffset As Integer

Range(FirstHeaderEntry) = Range(FirstDataItem)
LastHeader = Range(FirstDataItem).Value
ColumnOffset = 1
RowOffset = 1
Do Until IsEmpty(Range(FirstDataItem).Offset(RowOffset, 0))

If Range(FirstDataItem).Offset(RowOffset, 0) <> LastHeader Then

'entries have changed copy it and update pointers
LastHeader = Range(FirstDataItem).Offset(RowOffset, 0)
Range(FirstHeaderEntry).Offset(0, ColumnOffset).Value = LastHeader
ColumnOffset = ColumnOffset + 1

End If
RowOffset = RowOffset + 1

Loop
End Sub

use [Alt]+[F11] to open up the VB Editor, use Insert | Module to start a
code module and cut and paste this code into it. Make changes to the two
starting cell addresses as needed. Choose the sheet, sort your data, run the
macro.

"Jan" wrote:

> In an excel worksheet, I have repeated data in column 1. Now I want to sort
> it and make the data in column 1 the headers. Difficult to explain, but if I
> can send you my excel sheet you will understand it right away.....
> Hope somebody can help.....
> Jan Wind