Indexing a row

[ecohen1]

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

Please Login or Register  to view this content.

.....
.....


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

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ecohen1]

It's working for the first cost center, but after I just have some blanks.
Please could you send me an excel file with a sample to [email protected]

Thanks for your help

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[ecohen1]

Thanks you so much for your help, and again for your file, but again it doesn't work for me. I don't have exactly 20 rows for each center, I could have 20 for one cost center and 50 for example for the next one.

I'm sending you the excel file by Email, you gona see excatly the sheet.

Thanks again, I really appreciate your help.

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

> Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=

Note that the link may not work when clicked directly. If so, try a copy >
paste of the entire line (including the "=" at the end) into the browser's
Address box
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

The caveats on the data regularity were there as stated for the suggestions
to work. (Never said I had a solution otherwise)

Here's what I did in your sample (File returned to you)

Inserted a new col A
Placed in the "new" A1: =IF(C1="Cost Center:",E1+0,"")
Copied down to A11986 (the last data row)

Col A pulled in the 4 digit numbers from col E
where the marker phrase: "Cost Center:" appeared in col C

Killed the formulas in col A
(via an in-place copy > paste special > values > ok)

Inserted a new row1
Typed a label into A1
Did a Data > Filter > Autofilter on col A
Selected (Blanks) from the droplist in A1
Selected the filtered range A3:A11987 (blue row headers)
Right-clicked on the selection > Clear Contents
Removed the autofilter

Raided Debra's page on: Excel -- Data Entry -- Fill Blank Cells
at: http://www.contextures.com/xlDataEntry02.html

Under the section "Fill Blank Cells Programmatically"
(scroll down a bit in the page)
Copied and implemented Dave Peterson's
Sub FillColBlanks() into the book

Selected A2:A11987
Ran Dave's Sub FillColBlanks() on the selected range

(Think that's it. I'm out of ideas)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you so much for your help, and again for your file, but again it
> doesn't work for me. I don't have exactly 20 rows for each center, I
> could have 20 for one cost center and 50 for example for the next one.
>
> I'm sending you the excel file by Email, you gona see excatly the
> sheet.
>
> Thanks again, I really appreciate your help.
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Shift cells up > OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

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)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

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

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
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 <at>yahoo<dot>com
----

"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> It's working for the first cost center, but after I just have some
> blanks.
> Please could you send me an excel file with a sample to
> [email protected]
>
> Thanks for your help
>
>
> --
> ecohen1
> ------------------------------------------------------------------------
> ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
> View this thread: http://www.excelforum.com/showthread...hreadid=388070
>

[Max]

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 <at>yahoo<dot>com
----
"ecohen1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>