Automating an Organizing and Tabulating Nightmare

R

RalphSE

:eek:

Hi!

I am doing a daily task in excel that takes me at least 2 hours that
am quite sure can be automated in Excel, if not 100% at least 70% o
so. It would take me 5 pages to explain the task in here. What I di
instead is described it in a sample file here
http://www.flypicture.com?display=updone&id=rtjxlKTd.

I would be most grateful if any brave souls out there would help m
tackle this one.

THANKS!

:
 
K

kcc

In cell Step2!B7 put
=SUMPRODUCT((A7=START!B$2:B$295)*(START!C$2:C$295>0))/SUMPRODUCT(--(A7=START!B$2:B$295))
This assumes no change is counted as negative. If you truly want only
positives and negatives in the denominator use
=SUMPRODUCT((A7=START!B$2:B$295)*(START!C$2:C$295>0))/(SUMPRODUCT((A7=START!B$2:B$295)*(START!C$2:C$295>0))+SUMPRODUCT((A7=START!B$2:B$295)*(START!C$2:C$295<0)))
You may want to add an IF to check for #DIV/0.

kcc
 
P

Pete_UK

I've just had a quick look at it, but I think this can be automated.
Presumably you have a complete list of the sub-industries - do you want
to report on all that you have stocks in, even if there is no net
change overall?

Incidentally, you have omitted Aerospace/Defence in your final sheet -
obviously human error is a major factor in what you do now.

I'll just get something to eat, and return in a few hours.

Pete
 
P

Pete_UK

Oh, I see that kcc has replied while I was looking at your sheet. Never
mind then.

Pete
 
R

RalphSE

KCC, I apologize, I dont understand what you are suggesting or if I wa
clear enough about what I'm trying to accomplish:

worksheet "START" is basically my raw data, the final product look
like worksheet "STEP 2" which contains an alphabetical list of th
subindustries with the ADVANCING % in the 2nd column for eac
subindustry, you suggested I put that formula (which i have no ide
what its doing, perhaps explain that a little bit please?) in cell B2
but what about all the other subindustries, I guess it would help i
you explained in a bit more detail what you are suggesting

I'm sorry, am very excel challenged :
 
R

RalphSE

PETE, thanks for writing, you bring up a good point, ideally I woul
have 3 columns as an end result, the one that I already described whic
is:

(# issues advancing)/(# issues advancing + # issues declining) for eac
subindustry

but also, what would be fantastic would be a 3rd column that has:

(# issues advancing - # issues declining)/total # issues, total
issues would include ones that have zero net change for eac
subindustr
 
R

RalphSE

oh and yes PETE, you're right, there is human error involved, massiv
human error I'm sure, yet another reason why I hope you and KCC help m
out here!!

:) :) :) :) :
 
R

RalphSE

looks like I may have lost Pete abd KCC, anyone else out there have an
suggestions please
 
P

Pete_UK

I'm back again!

I would envisage that you have a Master workbook, which will comprise
one sheet into which you paste your data each day (equivalent to your
START sheet in the example), and a second sheet which will have a list
of all your sub-industries and the necessary formulae. After copying
the data into the Master workbook each day, you will save the file with
a different name, perhaps incorporating the date. The file will give
you what you want within a few minutes, so what will you find to do in
the 2 hours you spend now?

To set up the Master workbook, just use one of your current START
sheets and insert a new worksheet - let's call it Summary. Here are
some headings to put in row 1 of the Summary sheet:

Sub-industry, Number +ves, Number zeroes, Number -ves

in columns A to D. In column A you will have to list all your
sub-industries, as in your STEP 2 sheet, then the following formulae
can be entered:

B2: =SUM(IF(($A2=START!$B$2:$B$295)*(START!$C2>0),1,0))

C2: =SUM(IF(($A2=START!$B$2:$B$295)*(START!$C2=0),1,0))

D2: =SUM(IF(($A2=START!$B$2:$B$295)*(START!$C2<0),1,0))

These are array formulae, so you will have to type them as they appear
here (or copy/paste them from this message) then press CTRL SHIFT and
ENTER at the same time, rather than ENTER. If you do it correctly, the
formulae will have curly braces { } around them.

Will continue later ..

Pete
 
P

Pete_UK

.... sorry about the intermission - dinner time here in the UK!

Highlight the cells B2 to D2, set the format to number with 0 decimal
places, then copy down for as many sub-industries as you have in column
A (I think you said there were about 250 of them). As these columns
return the count of positive changes, zero changes and negative
changes, you can set up whatever other formulae you like in columns E
onwards to suit your percentages - again, copy these down.

The ranges in the array formulae refer to the data in your test sample,
and so these will need to be adjusted each day after you have copied
the new data in - however, if you always bring in the same number of
raw data rows, you can change them in the Master. Highlight columns B,
C and D and then do CTRL-H (Find/Replace) - in the Search For box type
$295 and in the Replace With box type $7500 (or whatever), then click
Replace All.

Highlight all the data in the START sheet and press <delete>. Save this
as the Master file, and it might be a good idea to save it with another
name (eg Master2), so that if you forget to rename it with new data you
always have one to fall back on.

So, in summary, each day you should open the Master file, copy your new
data into it, adjust the ranges in the Summary sheet (unless these are
always the same), and save the file with a new name. It may take a few
minutes for the formulae to re-calculate, but it will certainly be
quicker than your current manual method.

Hope this works for you.

Pete
 
R

RalphSE

as an example Pete, to show that its not working I sorted the STAR
sheet by subindustry and analyzed subindustry "ENERGY - Oil & Ga
Equipment & Services", I found 15 declines, 2 breakevens and 3
advances, however on the summary sheet it shows 50 advances,
breakevens and 0 declines...

at least they are both totaling 50 tickers, something must be wron
with the formulas or the way I copied them..
 
P

Pete_UK

Sorry, slight amendment needed to the array formulae as follows:

B2: =SUM(IF(($A2=START!$B$2:$B$7500)*(START!$C$2:$C$7500>0),1,0) )

C2: =SUM(IF(($A2=START!$B$2:$B$7500)*(START!$C$2:$C$7500=0),1,0) )

D2: =SUM(IF(($A2=START!$B$2:$B$7500)*(START!$C$2:$C$7500<0),1,0) )

As before, you need to confirm these with CTRL SHIFT and ENTER, then
copy them down.

Good luck - let me know how you get on.

Pete
 
P

Pete_UK

Well, Pete is such a fine name anyway ...

With the Master file effectively being a template, it will pay you to
get things exactly as you want them. Of course, you could just put a
SUM( ) formula on the rows where your Main Industries occur, though you
would have to adjust the range each time. Alternatively, the following
formulae will do it for you (insert a new column A first, so you have
space for your codes):

C2:
=SUM(IF(($B2=LEFT(START!$B$2:$B$7500,LEN($B2)))*(START!$C$2:$C$7500>0),1,0)
)


D2:
=SUM(IF(($B2=LEFT(START!$B$2:$B$7500,LEN($B2)))*(START!$C$2:$C$7500=0),1,0)
)


E2:
=SUM(IF(($B2=LEFT(START!$B$2:$B$7500,LEN($B2)))*(START!$C$2:$C$7500<0),1,0)
)


Again, CTRL SHIFT ENTER, then copy down

You might like to continue the shading across the columns with the
headings in, maybe bolded with a red foreground, then you can use the
format painter to reproduce this format for all the other major
categories - as I said, get it just how you want it in the Master file.
This will take slightly longer to re-calculate, so you might like to
change the 7500 to 7000 as long as this covers your data set - it's
only a matter of a few seconds on my machine, so it's not a big delay
anyway.

So, hopefully this is what you want.

Pete
 
R

RalphSE

i'll have a look at your last suggestion probably tomorrow Pete, but let
me tell you, as it is I am 99.99% there thanks to you, what an
incredibly cool thing you've done Pete, thanks a million my friend!!
you're a genius!

:)
 
P

Pete_UK

Well thanks for the praise, but steady on there ... - I just have a few
more years' experience of using Excel than you have (British reserve,
it's called!)

Besides, if you look at KCC's suggestion, you will find that it is not
very different to mine in posting number 14. These are the equivalent
formulae:

=SUMPRODUCT((A25=START!B$2:B$295)*(START!C$2:C$295>0))

=SUMPRODUCT((A25=START!B$2:B$295)*(START!C$2:C$295=0))

=SUMPRODUCT((A25=START!B$2:B$295)*(START!C$2:C$295<0))

and he'd used SUMPRODUCT whereas I used array formulae - exactly the
same results.

Incidentally, my posting 14 was in response to your 11 and 12, and 15
in response to your 13 (hope that makes sense!).

Maybe you can ask your boss for a pay rise tomorrow!

Pete
 
K

kcc

Sorry I wasn't around for all the intervening posts.
The formula I gave was for cell B7 since that was the that was the example
that was calculated in the file. It would need to be copied to the other
cells
in the column.
If you got Pete's formulas working, I would stick with them.
The only advantage to using sumproduct is you don't have to
press Ctrl-Shift-Enter.
kcc
 
Top