Indexing a row

E

ecohen1

I have an excel sheet with more than 10 000 lines, here's the strutur
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 ut
Code
-------------------

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


.....
.....


My question is how can I index each row, I would like to put the cos
center number before each row , because i would like to generate som
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.2
 
M

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
Click to expand...

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
Click to expand...
 
E

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
(e-mail address removed)

Thanks for your help
 
M

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
Click to expand...
 
E

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

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)
 
Top