table of contents
 

is not clear yet

"Mohamed" wrote:

How can i make table of contents for 15000 row, for example
year 2001
row 1

year 2002
row 5000

year 2003
row 10000

table of contents
 

is not clear yet

Open your file in excel. Make a new sheet for the contents. Rename it
'CONTENTS'.
Menus: Tools-Macro-Visual Basic Editor
In top left panel, double click ThisWorkbook

Paste the following code in right panel:

sub MakeContents()
dim entryRow as long
dim dataRow as long
dim lastYear as long
dim thisYear as long
dim lastRow as long

entryRow = 1
lastYear = 0

lastRow = Sheets("MAIN").Range("A:A").Count

for dataRow = 1 to lastRow
thisYear = Sheets("MAIN").Cells( dataRow, 1).Value
if thisYear<lastYear then
Sheets("CONTENTS").Cells( entryRow, 1).Value = "Year " + _

Format( thisYear)
Sheets("CONTENTS").Cells( entryRow + 1, 1).Value = dataRow
lastYear = thisYear
entryRow = entryRow + 3
endif
next
end sub

replace anywhere I've put a sheet name of MAIN with the name of the sheet
which contains your data. If the years are not held in column 1 (i.e.
column A), then you will have to change the lines:

lastRow = Sheets("MAIN").Range("A:A").Count - replace
"A:A"

thisYear = Sheets("MAIN").Cells( dataRow, 1).Value - replace
( dataRow, 1) - change the 1 to the correct column number


close the visual basic editor
in excel, go to tools-Macro-Macros...
double click on the entry which has MakeContents

Iain King