Not sure, but no harm giving this play a try on a spare copy of your sheet
...

The method assumes the data is in col A, from row1 down, with each set of
data comprising exactly 22 consecutive lines, starting from the line with
"Cost Center: .." until the blank row just before the next "Cost Center: .."
line. It's also assumed that the key number for the labelling is a 4 digit
number which appears after the colon-space in the line "Cost Center: xxxx
...." within each set of data, i.e. the "xxxx"

Cost Center: 5496 obsus << 1st line of 1st data set (in A1)

10:00 fluorouracil- 5000mg/100ml vial
10:00 irinotecan- 100mg/5ml vial 21
92:00 folinic acid inj- 500mg/50ml vial 90
Sub Account: 470102 Subtotal:
24:04.08 digoxin- 0.5mg/2ml ampul 10
Sub Account: 470112 Subtotal:
40:12 potassium chloride inj- 20meq/10ml vial
Sub Account: 470115 Subtotal: 8.69
56:22.20 ondansetron inj- 40mg/20ml vial
Sub Account: 470117 Subtotal:
68:04 dexamethasone inj- 10mg/ml vial
Sub Account: 470118 Subtotal: 0.65
38:00 glutaraldehyde 3.8l- 2% solution 4
84:04.92 chlorhexidine soap- 4%-4.5l solution
96:00 lubricating jelly- 150 g gel 20
Sub Account: 489609 Subtotal:
Charge Cost: 2386.21
Credit Cost: 0
Total Net Cost: 2386.21

Cost Center: 6021 4 utt << 1st line of 2nd data set (in A23)
etc

Insert 2 new cols to the left of the data
In the new cols A & B:

Put in A1:
=OFFSET(INDIRECT("B"&INT((ROWS($A$1:A1)-1)/22)*22+1),,)

Put in B1:
=IF(ISNUMBER(SEARCH("Cost Center:",C1)),MID(C1,SEARCH(": ",C1)+2,4)+0,"")

Select A1:B1, fill down until 1 row just beyond the last line: "Total Net
Cost: ..." of the last data set

Col A should return the labelling that you're after

Kill the formulas in both cols A & B with an "in-place" copy paste special
values ok, then delete col B
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ecohen1" wrote in
message ...

I have an excel sheet with more than 10 000 lines, here's the struture
of my sheet:

Cost Center: 5496 obsus

10:00 fluorouracil- 5000mg/100ml vial
10:00 irinotecan- 100mg/5ml vial 21
92:00 folinic acid inj- 500mg/50ml vial 90
Sub Account: 470102 Subtotal:
24:04.08 digoxin- 0.5mg/2ml ampul 10
Sub Account: 470112 Subtotal:
40:12 potassium chloride inj- 20meq/10ml vial
Sub Account: 470115 Subtotal: 8.69
56:22.20 ondansetron inj- 40mg/20ml vial
Sub Account: 470117 Subtotal:
68:04 dexamethasone inj- 10mg/ml vial
Sub Account: 470118 Subtotal: 0.65
38:00 glutaraldehyde 3.8l- 2% solution 4
84:04.92 chlorhexidine soap- 4%-4.5l solution
96:00 lubricating jelly- 150 g gel 20
Sub Account: 489609 Subtotal:
Charge Cost: 2386.21
Credit Cost: 0
Total Net Cost: 2386.21


Cost Center: 6021 4 utt
Code:
--------------------

--------------------


....
....


My question is how can I index each row, I would like to put the cost
center number before each row , because i would like to generate some
reports in Access.

example:


Cost Center: 5496 obsus

*5496* 10:00 fluorouracil- 5000mg/100ml vial
*5496* 10:00 irinotecan- 100mg/5ml vial
*5496* 92:00 folinic acid inj- 500mg/50ml vial
*5496* Sub Account: 470102 Subtotal:
*5496* 24:04.08 digoxin- 0.5mg/2ml ampul
*5496* Sub Account: 470112 Subtotal:
*5496* 40:12 potassium chloride inj- 20meq/10ml vial
*5496* Sub Account: 470115 Subtotal: 8.69

*5496* Charge Cost: 2386.21
*5496* Credit Cost: 0
*5496 * Total Net Cost: 2386.21


--
ecohen1
------------------------------------------------------------------------
ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
View this thread: http://www.excelforum.com/showthread...hreadid=388070