Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change the data in columns into headers?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change the data in columns into headers?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change the data in columns into headers?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
change data in rows to be viewed in columns | Excel Discussion (Misc queries) |