Excel 2007
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]Row\Col[/TH]
[TH]
[TH]
[TH]
[TH]
[TH]
[TH]
[TH]
[TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]Formula:[/TD]
[TD="bgcolor: #FFFFFF"]Item[/TD]
[TD="bgcolor: #FFFFFF"]Description[/TD]
[TD="bgcolor: #FFFFFF"]Category[/TD]
[TD="bgcolor: #FFFFFF"]UOM[/TD]
[TD="bgcolor: #FFFFFF"]Qty[/TD]
[TD="bgcolor: #FFFFFF"]Unit Cost[/TD]
[TD="bgcolor: #FFFFFF"]Extended Value[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]KIT, OVERHAUL, 22 ITEMS PER KIT, OVERHAUL KIT 10000 HOUR ( COMPRESSOR ) SPARE PARTS, FOR GAS COMPRESSOR K-2901A-I, EACH KIT CONSISTS OF THE FOLLOWING ITEMS AND QUANTITY:,PARTIALS IS NOT ACCEPTED[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]KIT, OVERHAUL, 22 ITEMS PER KIT, OVERHAUL KIT 10000 HOUR ( COMPRESSOR ) SPARE PARTS, FOR GAS COMPRESSOR K-2901A-I, EACH KIT CONSISTS OF THE FOLLOWING ITEMS AND QUANTITY:[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-AJAX[/TD]
[TD="bgcolor: #FFFFFF"]KIT[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]PARTIALS IS NOT ACCEPTED[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-AJAX[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]Model: 24312J23/S1, 2" 600# Flange Inlet X 3" 150#,Flange Outlet, Carbon Steel Body, Stainless Steel,Trim, Set at 780 psi, "UV" Gas Service, with Field Test,Connection, Screwed Cap.[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]Model: 24312J23/S1, 2" 600# Flange Inlet X 3" 150#[/TD]
[TD="bgcolor: #FFFFFF"]VALVES-RELIEF[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Flange Outlet, Carbon Steel Body, Stainless Steel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Trim, Set at 780 psi, "UV" Gas Service, with Field Test[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Connection, Screwed Cap.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLS[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLS[/TD]
[TD="bgcolor: #FFFFFF"]VALVES-RELIEF[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"], 8000 H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]FOR AIR COMPRESSOR ATLAS COPCO GA 132 - 10/50-AIR COMPRESSED - (BOP)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SILENCER,FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]SILENCER[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-ATLAS COPC[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]#N/A[/TD]
[TD="bgcolor: #FFFFFF"]Y00501100[/TD]
[TD="bgcolor: #FFFFFF"]Master Control Unit, for Advance Control Cabinet, Page/Party system, ITR Building[/TD]
[TD="bgcolor: #FFFFFF"]ITR BUILD-MATERIALS[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[TD="bgcolor: #FFFFFF"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]Above data was exported from a text file, some items are ok (with one row/ record), some have multi rows of description.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]The problem is that the file is 30,000 rows, which crashes Excel. How can I have a helper cell or (a better formula)?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]Below formula (entered in Cell A7 w/shift+ctrl), it counts the # of description rows, then[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]concatenate those rows to make them as a one-row description. (Ideas taken from your feed back guys, thanks!)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]=IF(ISBLANK(B7),"",IF(AND(ISNUMBER(B7),MATCH(1,--(LEN(B8:B$31002)>0),0)>5),C7&","&C8&","&C9&","&C10&","&C11,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD] CHOOSE(IF(B7="","",MATCH(1,--(LEN(B8:B$31002)>0),0)),C7,C7&","&C8,C7&","&C8&","&C9,C7&","&C8&","&C9&","&[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD] C10,C7&","&C8&","&C9&","&C10&","&C11)))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]I know it's gonna take time from you guys, but bear with me please, I'm almost done with this mess. Appreciate your help.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Inventory_Average_Cost_R_[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]Row\Col[/TH]
[TH]
A
[/TH][TH]
B
[/TH][TH]
C
[/TH][TH]
D
[/TH][TH]
E
[/TH][TH]
F
[/TH][TH]
G
[/TH][TH]
H
[/TH][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]
Inventory Value Report
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6
[/TD][TD]Formula:[/TD]
[TD="bgcolor: #FFFFFF"]Item[/TD]
[TD="bgcolor: #FFFFFF"]Description[/TD]
[TD="bgcolor: #FFFFFF"]Category[/TD]
[TD="bgcolor: #FFFFFF"]UOM[/TD]
[TD="bgcolor: #FFFFFF"]Qty[/TD]
[TD="bgcolor: #FFFFFF"]Unit Cost[/TD]
[TD="bgcolor: #FFFFFF"]Extended Value[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7
[/TD][TD]KIT, OVERHAUL, 22 ITEMS PER KIT, OVERHAUL KIT 10000 HOUR ( COMPRESSOR ) SPARE PARTS, FOR GAS COMPRESSOR K-2901A-I, EACH KIT CONSISTS OF THE FOLLOWING ITEMS AND QUANTITY:,PARTIALS IS NOT ACCEPTED[/TD]
[TD="bgcolor: #FFFFFF"]
500100
[/TD][TD="bgcolor: #FFFFFF"]KIT, OVERHAUL, 22 ITEMS PER KIT, OVERHAUL KIT 10000 HOUR ( COMPRESSOR ) SPARE PARTS, FOR GAS COMPRESSOR K-2901A-I, EACH KIT CONSISTS OF THE FOLLOWING ITEMS AND QUANTITY:[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-AJAX[/TD]
[TD="bgcolor: #FFFFFF"]KIT[/TD]
[TD="bgcolor: #FFFFFF"]
8
[/TD][TD="bgcolor: #FFFFFF"]
5,727.16
[/TD][TD="bgcolor: #FFFFFF"]
45,817.24
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8
[/TD][TD][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]PARTIALS IS NOT ACCEPTED[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9
[/TD][TD]KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)[/TD]
[TD="bgcolor: #FFFFFF"]
500200
[/TD][TD="bgcolor: #FFFFFF"]KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-AJAX[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
1
[/TD][TD="bgcolor: #FFFFFF"]
6,695.12
[/TD][TD="bgcolor: #FFFFFF"]
6,695.12
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10
[/TD][TD]Model: 24312J23/S1, 2" 600# Flange Inlet X 3" 150#,Flange Outlet, Carbon Steel Body, Stainless Steel,Trim, Set at 780 psi, "UV" Gas Service, with Field Test,Connection, Screwed Cap.[/TD]
[TD="bgcolor: #FFFFFF"]
131580560
[/TD][TD="bgcolor: #FFFFFF"]Model: 24312J23/S1, 2" 600# Flange Inlet X 3" 150#[/TD]
[TD="bgcolor: #FFFFFF"]VALVES-RELIEF[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
2
[/TD][TD="bgcolor: #FFFFFF"]
2,919.06
[/TD][TD="bgcolor: #FFFFFF"]
5,838.11
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Flange Outlet, Carbon Steel Body, Stainless Steel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Trim, Set at 780 psi, "UV" Gas Service, with Field Test[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]Connection, Screwed Cap.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14
[/TD][TD]VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLS[/TD]
[TD="bgcolor: #FFFFFF"]
131580570
[/TD][TD="bgcolor: #FFFFFF"]VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLS[/TD]
[TD="bgcolor: #FFFFFF"]VALVES-RELIEF[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
2
[/TD][TD="bgcolor: #FFFFFF"]
304.76
[/TD][TD="bgcolor: #FFFFFF"]
609.51
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"], 8000 H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]FOR AIR COMPRESSOR ATLAS COPCO GA 132 - 10/50-AIR COMPRESSED - (BOP)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18
[/TD][TD]SILENCER,FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)[/TD]
[TD="bgcolor: #FFFFFF"]
810000800
[/TD][TD="bgcolor: #FFFFFF"]SILENCER[/TD]
[TD="bgcolor: #FFFFFF"]COMPRESSOR-ATLAS COPC[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
4
[/TD][TD="bgcolor: #FFFFFF"]
341.67
[/TD][TD="bgcolor: #FFFFFF"]
1,366.68
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
19
[/TD][TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF"]FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
20
[/TD][TD]#N/A[/TD]
[TD="bgcolor: #FFFFFF"]Y00501100[/TD]
[TD="bgcolor: #FFFFFF"]Master Control Unit, for Advance Control Cabinet, Page/Party system, ITR Building[/TD]
[TD="bgcolor: #FFFFFF"]ITR BUILD-MATERIALS[/TD]
[TD="bgcolor: #FFFFFF"]EA[/TD]
[TD="bgcolor: #FFFFFF"]
2
[/TD][TD="bgcolor: #FFFFFF"]
3,860.00
[/TD][TD="bgcolor: #FFFFFF"]
7,720.00
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
21
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
22
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
23
[/TD][TD]Above data was exported from a text file, some items are ok (with one row/ record), some have multi rows of description.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
24
[/TD][TD]The problem is that the file is 30,000 rows, which crashes Excel. How can I have a helper cell or (a better formula)?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
25
[/TD][TD]Below formula (entered in Cell A7 w/shift+ctrl), it counts the # of description rows, then[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
26
[/TD][TD]concatenate those rows to make them as a one-row description. (Ideas taken from your feed back guys, thanks!)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
27
[/TD][TD]=IF(ISBLANK(B7),"",IF(AND(ISNUMBER(B7),MATCH(1,--(LEN(B8:B$31002)>0),0)>5),C7&","&C8&","&C9&","&C10&","&C11,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
28
[/TD][TD] CHOOSE(IF(B7="","",MATCH(1,--(LEN(B8:B$31002)>0),0)),C7,C7&","&C8,C7&","&C8&","&C9,C7&","&C8&","&C9&","&[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
29
[/TD][TD] C10,C7&","&C8&","&C9&","&C10&","&C11)))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
30
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
31
[/TD][TD]I know it's gonna take time from you guys, but bear with me please, I'm almost done with this mess. Appreciate your help.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Inventory_Average_Cost_R_[/TD]
[/TR]
</tbody>[/TABLE]