Need a helper celll or a better formula which won't crash Excel.

snd

New Member
Joined
Jan 24, 2010
Messages
44
Excel 2007
[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]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top