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

snd

New Member
Joined
Jan 24, 2010
Messages
44
Excel 2007
Row\Col
A
B
C
D
E
F
G
H
1
Inventory Value Report
2
3
4
5
6
Formula:ItemDescriptionCategoryUOMQtyUnit CostExtended Value
7
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
500100​
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:COMPRESSOR-AJAXKIT
8​
5,727.16​
45,817.24​
8
PARTIALS IS NOT ACCEPTED
9
KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)
500200​
KIT, OVERHAUL, CONSISTS OF THE FOLLOWING (26 ITEMS), FOR GAS COMPRESSOR, (NO PARTIAL DELIVERY)COMPRESSOR-AJAXEA
1​
6,695.12​
6,695.12​
10
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.
131580560​
Model: 24312J23/S1, 2" 600# Flange Inlet X 3" 150#VALVES-RELIEFEA
2​
2,919.06​
5,838.11​
11
Flange Outlet, Carbon Steel Body, Stainless Steel
12
Trim, Set at 780 psi, "UV" Gas Service, with Field Test
13
Connection, Screwed Cap.
14
VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLS
131580570​
VALVE, RELIEF, SERIES 69F, BRONZE 2IN 350PSIG SET 150PSI, CAPACITY 120GPM, MALE INLET, FEMALE OUTLET, FOR WATER WELLSVALVES-RELIEFEA
2​
304.76​
609.51​
16
, 8000 H
17
FOR AIR COMPRESSOR ATLAS COPCO GA 132 - 10/50-AIR COMPRESSED - (BOP)
18
SILENCER,FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)
810000800​
SILENCERCOMPRESSOR-ATLAS COPCEA
4​
341.67​
1,366.68​
19
FOR HEATLESS DRYER ATLAS COPCO 5055827-P01 CD 1050-AIR COMPRESSED - (BOP)
20
#N/AY00501100Master Control Unit, for Advance Control Cabinet, Page/Party system, ITR BuildingITR BUILD-MATERIALSEA
2​
3,860.00​
7,720.00​
21
22
23
Above data was exported from a text file, some items are ok (with one row/ record), some have multi rows of description.
24
The problem is that the file is 30,000 rows, which crashes Excel. How can I have a helper cell or (a better formula)?
25
Below formula (entered in Cell A7 w/shift+ctrl), it counts the # of description rows, then
26
concatenate those rows to make them as a one-row description. (Ideas taken from your feed back guys, thanks!)
27
=IF(ISBLANK(B7),"",IF(AND(ISNUMBER(B7),MATCH(1,--(LEN(B8:B$31002)>0),0)>5),C7&","&C8&","&C9&","&C10&","&C11,
28
CHOOSE(IF(B7="","",MATCH(1,--(LEN(B8:B$31002)>0),0)),C7,C7&","&C8,C7&","&C8&","&C9,C7&","&C8&","&C9&","&
29
C10,C7&","&C8&","&C9&","&C10&","&C11)))
30
31
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.

<tbody>
</tbody>
Sheet: Inventory_Average_Cost_R_

<tbody>
</tbody>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Threads
1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

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
Top