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
..
..
..

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
.
.
.

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
.
.
.

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
.
.
.

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