Sorting by month

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Good morning,
I have several lines of information for different plants and am trying to
consolidate it to one line per plant.

ie:
a b c
7/21 A 123
7/21 B 342
7/21 C 502
7/22 B 132
7/22 C 200
7/25 A 400

What I want to see is this...

a b c
July A 523 <~~~Total for month for that plant
july B 474
July C 702

I would like to have it do this for each month but on the same page. I hope
this makes sense.
 
Hey Bernd,
Thank you for your help. I had to figure out what the sfreq was in Excel
2007 but I finally figured it out. It is sumfs just in case you were
wondering. On to the Macro part....
I am confused with what you are telling me to do. Output #3 on your web
page is what I am looking to do. Sorry I am being difficult but any more
help would be helpful. Thank you

Eric
 
Hey Bernd,
Thank you for your help. I had to figure out what the sfreq was in Excel
2007 but I finally figured it out. It is sumfs just in case you were
wondering. On to the Macro part....
I am confused with what you are telling me to do. Output #3 on your web
page is what I am looking to do. Sorry I am being difficult but any more
help would be helpful. Thank you

Eric
 
Hello Eric,

If you use Sumproduct or Sumfs you still need to feed your formulae
the names of your plants and the months/years.

If you just want to create a statistic on all data, automatically
summing up everything, take Pfreq, Mfreq or Pstat of my website, I
suggest...

Regards,
Bernd
 
Hello once again Bernd,

The Sumfs works great! I thank you for the information. But this isn't
exactly how I wanted it to work. I am trying to get this into a macro. The
way it is now it is using up a lot of line space because I have 13 different
plants and not all the plants are operating every month. So If I do the
Sumfs for every month and for every plant I will have a lot of wasted lines.
Because I need to list out each plant in it's own line for each month.
How can I make a macro do a summary report for each month (Cronilogicaly)
and not waste paper by listing every plant and putting zeros when there is no
production for that given month for that given plant?

Eric
 
Bernd,
I looked at the macro before the last posting but thought I was doing
something wrong because I kept getting and error code (sub or function not
defined) on the line v(0) = .Transpose(.Transpose(v(0))). Do I need to DIM V
(0) prior to this line because when I do then I get a different error message
about not being able to "Exit Function".
Unfortunetly, I am no where near as experienced as you are in dealing with
macros, actualy I am a beginner. Sorry for the hassles.
 
Hello Eric,

The macros are intended to be used as they are - just press ALT + F11
in your Excel worksheet, enter a new module and copy my macro text in.
Then go back to your worksheet and enter the array formulas as shown
in my Examples (enter with CTRL + SHIFT + ENTER, not only with ENTER).

Again: If its easier to send me a file I am happy to complete a short
and an instructive example in your file.

Regards,
Bernd
 
Bernd,
Thank you thank you thank you. I figured it out. You are the best and it
is exactly what I am looking for......
 
Bernd,
If I wanted to make the macro cover 4 or 5 columns instead of 3 what would
that macro look like. Right now what I am doing is using your current sfreq
macro twice to get the 5 columns I need. I minimize one column so it can't
be seen. Just wondering if you can expand the macro to cover 5 columns.
Thank you
 
Hello Eric,

Thanks for your feedback.

For 5 columns the macro does not need to be changed. Just change the
call to the macro:
=Sfreq(A1:A99,B1:B99,C1:C99,D1:D99,E1:E99)
[array-entered again, please keep in mind that the last entered column
is containing the VALUES you want to sum up.]

If you have many rows (tens of thousands) let me optimize the memory
strategy first. With less it should work fine.

Regards,
Bernd
 
Hey Bernd,
I hope you enjoyed the weekend. I am still working on the spreadsheetbut
now have run into another problem. (It's amazing how these sheets grow and
never seem to have an end point)

I am doing the same sort of sorting just like you helped me with before but
now I am needing to transphere 6 rows of information but sum up each row
based on the criterias.
example:

A B C D E
7/1 N.B. 6 3 4
7/2 N. B. 3 5 1
7/2 Gro 4 2 3

The finished product would look like this:

A B C D E
July N.B. 9 8 5
GRO 4 2 3

I hope this makes sense. I know the sfreq would only total the last column
for everything not based on certain criterias.


Eric
 
Back
Top