ExcelBanter - Copy and SUM functions with different ranges

Nel post
*Manos* ha scritto:

Dear all

I am attaching an excel file looking your help in two areas.
The first one is to copy a code from one cell to folloning cells with
different ranges. Eg. a code form A8 to A9till A15, a code form A25
to A26 till A32 and so on.

The second problem can be solved with a sumif. I have sum values eg
from E10 to E15 and i want to have the sum to E16, then form E20 till
to E32 to sumup to E33 and so on.

The problem is that the file is uite big, exported from ERP and has
48000 lines

Any solution to any of the two problems would be great.

Thank you in advance
Manos

Hi Manos,

First of all, next time don't attach any file when posting. It's better to
upload to a public hosting service, such as for example www.savefile.com.
Now, to solve your problem with codes, insert a blank column before column
A, then near the first code (should be B11 with your sample file) insert
this function (so insert the function in A11):

=IF(CODE(B11)=32,0,B11)

copy down as for the lenght of your records.
Now Copy column A and Past Special, Values. Now with an Autofilter filter on
column A for #VALUE! errors, select all of them and press the DEL Key on
your keyboard, then filter for 0 in column A, select all of them and press
the DEL Key on your keyboard.

Now switch off the Autofilter. Press the key CTRL+ END to go to the last
used cell, then press the HOME key to go to the first cell in that row, then
press CTRL + SHIFT + HOME to select the part of column used, then press
SHIFT + END to deselect the first part of column that is empty.

Now press F5 (or menu Edit, Go to) and click on Special. Select Blanks and
click OK.
Now (without changing selection, it's very important...) type = and press
the key Arrow Up, then press CTRL + ENTER.

Now select column A, Copy & Past Special, Values.

This is for the first part of your problem.

For the second part, I hope you don't need the Totals inserted from your
ERP, so use Autofilter to eliminate them. In this way you can use Subtotals
(menu Data, Subtotals) and insert subtotals by code.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy