Home |
Search |
Today's Posts |
#1
|
|||
|
|||
rapeat heading row after each alternate row
Kindly tel me how can i be able to put the header row before each row of a
data base contains about 20,000 rows in excel or access i.e. Employee name employee ID Address Asad 1007 xyz Employee name employee ID Address Jamil 1039 trb .. .. .. |
#2
|
|||
|
|||
in excel why is this necessary . if you highlight row before headings and
click windows(menu)-freezepanes the hedings row will remain as you scroll down even to 2000th row. If there is some other reason, sorry. ams0007 wrote in message ... Kindly tel me how can i be able to put the header row before each row of a data base contains about 20,000 rows in excel or access i.e. Employee name employee ID Address Asad 1007 xyz Employee name employee ID Address Jamil 1039 trb . . . |
#3
|
|||
|
|||
One way ..
Assume you have in Sheet1 -------------- in cols A to C, data from row1 down Asad 1007 xyz Jamil 1039 trb etc In Sheet2 ------------- Put in A2: =IF(MOD(ROW(A1),2)=1,OFFSET(Sheet1!$A$1,(ROW(A1)+1 )/2-1,COLUMN(A1)-1),"") Copy A2 across to C2, fill down by twice as many rows as there is data in Sheet1. E.g.: if Sheet1 has 20,000 rows of data, fill down to C40000 This'll "insert" an alternate "blank" row in-between the data from Sheet1 In Sheet3 ------------- Put In A1: =IF(Sheet2!A1="","Employee Name",Sheet2!A1) In B1: =IF(Sheet2!B1="","Employee ID",Sheet2!B1) In C1: =IF(Sheet2!C1="","Address",Sheet2!C1) Select A1:C1, copy down to C40000 The above will return what you're after, viz. for the sample data, you'll get in A1:C4: Employee Name Employee ID Address Asad_________1007________xyz Employee Name Employee ID Address Jamil_________1039________trb Kill the formulas in A1:C40000 with an-in place: Copy Paste special values OK (or just copy and paste special as values on another sheet) Select A1:C40000 To select the range, type in the Name Box*: A1:C40000, then press ENTER *the box with the drop-arrow just to the left of the formula bar Then right-click on the selected range Copy Right-click again on the range Paste special Values OK (this'll kill all the formulas in A1:C40000) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "ams0007" wrote in message ... Kindly tel me how can i be able to put the header row before each row of a data base contains about 20,000 rows in excel or access i.e. Employee name employee ID Address Asad 1007 xyz Employee name employee ID Address Jamil 1039 trb . . . |
#4
|
|||
|
|||
Hi Max,
Developing your idea further, one can probably do this in one step using the formula in B1 copied to cells below if the Name heading is in A1 and actual names are in A2 and below. =IF(MOD(ROW(),2)=1,$A$1,OFFSET($A$1,ROW()/2,0)) Alok "Max" wrote in message ... One way .. Assume you have in Sheet1 -------------- in cols A to C, data from row1 down Asad 1007 xyz Jamil 1039 trb etc In Sheet2 ------------- Put in A2: =IF(MOD(ROW(A1),2)=1,OFFSET(Sheet1!$A$1,(ROW(A1)+1 )/2-1,COLUMN(A1)-1),"") Copy A2 across to C2, fill down by twice as many rows as there is data in Sheet1. E.g.: if Sheet1 has 20,000 rows of data, fill down to C40000 This'll "insert" an alternate "blank" row in-between the data from Sheet1 In Sheet3 ------------- Put In A1: =IF(Sheet2!A1="","Employee Name",Sheet2!A1) In B1: =IF(Sheet2!B1="","Employee ID",Sheet2!B1) In C1: =IF(Sheet2!C1="","Address",Sheet2!C1) Select A1:C1, copy down to C40000 The above will return what you're after, viz. for the sample data, you'll get in A1:C4: Employee Name Employee ID Address Asad_________1007________xyz Employee Name Employee ID Address Jamil_________1039________trb Kill the formulas in A1:C40000 with an-in place: Copy Paste special values OK (or just copy and paste special as values on another sheet) Select A1:C40000 To select the range, type in the Name Box*: A1:C40000, then press ENTER *the box with the drop-arrow just to the left of the formula bar Then right-click on the selected range Copy Right-click again on the range Paste special Values OK (this'll kill all the formulas in A1:C40000) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "ams0007" wrote in message ... Kindly tel me how can i be able to put the header row before each row of a data base contains about 20,000 rows in excel or access i.e. Employee name employee ID Address Asad 1007 xyz Employee name employee ID Address Jamil 1039 trb . . . |
#5
|
|||
|
|||
Thanks, Alok !
Yes, building further from your suggestion, think it could be done in one step in Sheet2 via putting in A1 (in Sheet2): =IF(MOD(ROW(A1),2)=1,CHOOSE(COLUMN(A1),"Employee Name","Employee ID","Address"),OFFSET(Sheet1!$A$1,(ROW(A1)+1)/2-1,COLUMN(A1)-1)) then copying A1 across to C1, and filling down to C40000 (Above assumes data in Sheet1 is in cols A to C, from row1 down) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Alok Joshi" wrote in message ... Hi Max, Developing your idea further, one can probably do this in one step using the formula in B1 copied to cells below if the Name heading is in A1 and actual names are in A2 and below. =IF(MOD(ROW(),2)=1,$A$1,OFFSET($A$1,ROW()/2,0)) Alok |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum alternate columns over a large (>100) range | Excel Discussion (Misc queries) | |||
Shade alternate lines | Excel Discussion (Misc queries) | |||
How do I shade alternate rows in Excel 2002? | Excel Discussion (Misc queries) | |||
How do I set up a column with a heading and a subheading? | Excel Discussion (Misc queries) | |||
How do I create a personalized heading row in excel (one that rea. | Excel Worksheet Functions |