Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indexing a row
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 |
#2
|
|||
|
|||
Not sure, but no harm giving this play a try on a spare copy of your sheet
... The method assumes the data is in col A, from row1 down, with each set of data comprising exactly 22 consecutive lines, starting from the line with "Cost Center: .." until the blank row just before the next "Cost Center: .." line. It's also assumed that the key number for the labelling is a 4 digit number which appears after the colon-space in the line "Cost Center: xxxx ...." within each set of data, i.e. the "xxxx" Cost Center: 5496 obsus << 1st line of 1st data set (in A1) 10:00 fluorouracil- 5000mg/100ml vial 10:00 irinotecan- 100mg/5ml vial 21 92:00 folinic acid inj- 500mg/50ml vial 90 Sub Account: 470102 Subtotal: 24:04.08 digoxin- 0.5mg/2ml ampul 10 Sub Account: 470112 Subtotal: 40:12 potassium chloride inj- 20meq/10ml vial Sub Account: 470115 Subtotal: 8.69 56:22.20 ondansetron inj- 40mg/20ml vial Sub Account: 470117 Subtotal: 68:04 dexamethasone inj- 10mg/ml vial Sub Account: 470118 Subtotal: 0.65 38:00 glutaraldehyde 3.8l- 2% solution 4 84:04.92 chlorhexidine soap- 4%-4.5l solution 96:00 lubricating jelly- 150 g gel 20 Sub Account: 489609 Subtotal: Charge Cost: 2386.21 Credit Cost: 0 Total Net Cost: 2386.21 Cost Center: 6021 4 utt << 1st line of 2nd data set (in A23) etc Insert 2 new cols to the left of the data In the new cols A & B: Put in A1: =OFFSET(INDIRECT("B"&INT((ROWS($A$1:A1)-1)/22)*22+1),,) Put in B1: =IF(ISNUMBER(SEARCH("Cost Center:",C1)),MID(C1,SEARCH(": ",C1)+2,4)+0,"") Select A1:B1, fill down until 1 row just beyond the last line: "Total Net Cost: ..." of the last data set Col A should return the labelling that you're after Kill the formulas in both cols A & B with an "in-place" copy paste special values ok, then delete col B -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "ecohen1" wrote in message ... I have an excel sheet with more than 10 000 lines, here's the struture of my sheet: Cost Center: 5496 obsus 10:00 fluorouracil- 5000mg/100ml vial 10:00 irinotecan- 100mg/5ml vial 21 92:00 folinic acid inj- 500mg/50ml vial 90 Sub Account: 470102 Subtotal: 24:04.08 digoxin- 0.5mg/2ml ampul 10 Sub Account: 470112 Subtotal: 40:12 potassium chloride inj- 20meq/10ml vial Sub Account: 470115 Subtotal: 8.69 56:22.20 ondansetron inj- 40mg/20ml vial Sub Account: 470117 Subtotal: 68:04 dexamethasone inj- 10mg/ml vial Sub Account: 470118 Subtotal: 0.65 38:00 glutaraldehyde 3.8l- 2% solution 4 84:04.92 chlorhexidine soap- 4%-4.5l solution 96:00 lubricating jelly- 150 g gel 20 Sub Account: 489609 Subtotal: Charge Cost: 2386.21 Credit Cost: 0 Total Net Cost: 2386.21 Cost Center: 6021 4 utt Code: -------------------- -------------------- .... .... My question is how can I index each row, I would like to put the cost center number before each row , because i would like to generate some reports in Access. example: Cost Center: 5496 obsus *5496* 10:00 fluorouracil- 5000mg/100ml vial *5496* 10:00 irinotecan- 100mg/5ml vial *5496* 92:00 folinic acid inj- 500mg/50ml vial *5496* Sub Account: 470102 Subtotal: *5496* 24:04.08 digoxin- 0.5mg/2ml ampul *5496* Sub Account: 470112 Subtotal: *5496* 40:12 potassium chloride inj- 20meq/10ml vial *5496* Sub Account: 470115 Subtotal: 8.69 *5496* Charge Cost: 2386.21 *5496* Credit Cost: 0 *5496 * Total Net Cost: 2386.21 -- ecohen1 ------------------------------------------------------------------------ ecohen1's Profile: http://www.excelforum.com/member.php...o&userid=12988 View this thread: http://www.excelforum.com/showthread...hreadid=388070 |
#3
|
|||
|
|||
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 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 |
#4
|
|||
|
|||
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 <atyahoo<dotcom ---- "ecohen1" wrote in message ... 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 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 |
#5
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 <atyahoo<dotcom ---- "ecohen1" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Indexing the right option? | Excel Worksheet Functions | |||
Indexing an Array with VLOOKUP | Excel Worksheet Functions | |||
Indexing, matching and vlookups? | Excel Worksheet Functions | |||
Vlookup and Indexing in excel | Excel Worksheet Functions | |||
Vlookup - indexing | Excel Worksheet Functions |