Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
How to repeat column header for each row NAME JOB SALARY AAA CLERK 5000 BBB MANAGER 6000 CCC CASHIER 7000 I want result as: NAME JOB SALARY AAA CLERK 5000 NAME JOB SALARY BBB MANAGER 6000 NAME JOB SALARY CCC CASHIER 7000 -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=537447 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
Try:
Sub a() Dim title() As String With Worksheets("Sheet1") '<=== change as needed lastrow = .Cells(Rows.Count, "A").End(xlUp).Row titles = .Range("a1:c1") For r = lastrow To 3 Step -1 .Cells(r, 1).EntireRow.Insert .Cells(r, 1).Resize(1, 3) = titles Next r End With End Sub "jamex" wrote: How to repeat column header for each row NAME JOB SALARY AAA CLERK 5000 BBB MANAGER 6000 CCC CASHIER 7000 I want result as: NAME JOB SALARY AAA CLERK 5000 NAME JOB SALARY BBB MANAGER 6000 NAME JOB SALARY CCC CASHIER 7000 -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=537447 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
First, I think keeping the data in a nice tabular form is usually much better.
There are lots of things that will become more difficult if you do this. But if you want, you can use a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim HeaderRow As Long Set wks = Worksheets("sheet1") With wks HeaderRow = 1 FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 .Rows(HeaderRow).Copy .Rows(iRow).Insert Next iRow End With Application.CutCopyMode = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm jamex wrote: How to repeat column header for each row NAME JOB SALARY AAA CLERK 5000 BBB MANAGER 6000 CCC CASHIER 7000 I want result as: NAME JOB SALARY AAA CLERK 5000 NAME JOB SALARY BBB MANAGER 6000 NAME JOB SALARY CCC CASHIER 7000 -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=537447 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
Thanks topper, nice coding. Can we have other option to solve this problem other than you macro? -- jamex ------------------------------------------------------------------------ jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243 View this thread: http://www.excelforum.com/showthread...hreadid=537447 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
First...
In an adjacent column, consecutively number the rows and then repeat. Sort the data by the numbers to add a blank row between each row. NAME JOB SALARY 1 AAA CLERK 5000 2 BBB MANAGER 6000 3 CCC CASHIER 7000 4 DDD WIFE 8000 5 EEE CHILD 9000 1 2 3 4 5 Second... Copy the titles. Third... Select the column with the names (not the entire column) Fourth... Edit | GoTo | Special | Blanks Fifth... Paste Sixth... Delete the extra column -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "jamex" wrote in message How to repeat column header for each row NAME JOB SALARY AAA CLERK 5000 BBB MANAGER 6000 CCC CASHIER 7000 I want result as: NAME JOB SALARY AAA CLERK 5000 NAME JOB SALARY BBB MANAGER 6000 NAME JOB SALARY CCC CASHIER 7000 -- jamex |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating Column header for each row
jamex,
you can do it with formulas. Assuming your data (first header, NAME) starts at A1, and assuming that your output starts at E1, you can use the following formula in E1: =IF(MOD(ROW()-ROW(E$1),2)=0,A$1,OFFSET(A$1,1+(ROW()-ROW(E$1))/2,0)) This formula can be copied down and to the right, as far as necessary. Replace A1 and E1 to suit. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Find the first Value in a Row and Retrieve the Header for That Column | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions |