subtotal one column based on the range of two other columns

[jshot99]

Hey everyone,,

I asked on the general forum earlier how to do this only refering to one range and got the equation:

=COUNT(1/FREQUENCY(IF('Shipping and Invoice'!$J$2:$J$23548=B2+0,IF('Shipping and Invoice'!$L$2:$L$23548<>"",MATCH('Shipping and Invoice'!$L$2:$L$23548,'Shipping and Invoice'!$L$2:$L$23548,0))),ROW('Shipping and Invoice'!$L$2:$L$23548)-ROW('Shipping and Invoice'!$L$2)+1))

Now what this equation does is looks at column J which is a date range (B2 is the date that the user wants the data for). Based on that date range is only looks for the corresponding range of rows in Column L which are order numbers. Now there are multiple entires ofr a single order. So what the equation does is only count each order once. So there is a list of 5 5 5 4 4 6 6 3, the return count is 4 (once for each value), again only focus on the cells that corresponding to the date entered in B2

Well I need this same thing but also refering to another range. So IDENTICAL to what is above but adding in a time range so instead of just looking at the subtotal count for 4/25/11 I need to look at the subtotal count for 4/25/11 before 11 PM (And actually I need this for 4 distinct ranges that are the smae everything which are before 11, between 11 and 1, between 1 and 3:30 and after 3:30

I know the equaiton for just the data takes forever to calculate with the huge range (the range has to tbe the large overall) so I am guessing a macro makes more sense for this. Any advice?!!

Thanks!

[DonkeyOte]

The key to doing this "efficiently" with formulae will be sorting your data set by Order, Date & Time.

If this isn't viable you could do this with VBA using either a Dictionary Object or Collection - pushing the dataset into a VBA array and iterating - this might prove quicker with big data sets than formulae (assumes unsorted data).

I know the equaiton for just the data takes forever to calculate with the huge range

From the above I have assumed you don't want / need the revisions for your Array formula regards the Time criteria.

[jshot99]

Donkey, thanks for responding.

Please excuse the typos, shooting these posts off during a busy work day....

I am not to sure what you meant by the question but to sum up what I need:

Column J, Rows J1-J20000 (never will exceed 20,000 so I use this as default) have dates in it, Column AI, Rows AI1-AI20000 has time in it and Column L, Row L1-L20000 has order number in it.

The order numbers have duplicates in column L since there are multiple products for each order. I am trying to track the number of ORDERS (not products) processed per day for a certain time range.

So I need the equation or macro to look at column J range (1 to 20,000) and pull out the corresponding date that the user selected to look at that date for 4 specific time ranges (11 am and earlier, 11 am to 1 pm, 1pm to 330 pm, 330 pm and later). Those times ranges are always the same (so in theory I need 4 difference equations or Macros) and I want those 4 values to post in a table labeled withthe time ranges. The values are a count of the orders in column L that correspond to that date and time range, but only counting each order once!

Hope this is clear what I am trying to do. I am not experienced at all in excel, so I was hoping for a macro someone possibly already had or something someone could write up easily.

Thanks in advaance!!!

[DonkeyOte]

Yes I understand what you're trying to do but could not determine if you wanted to use (revised) Arrays or not ? It would seem you want to use VBA ?

As far as I can tell you have not outlined as to whether or not your data is sorted in any way ? If it is, how exactly ?

Assuming data is unsorted...

If you did want to use Arrays it would simply be a case of inserting some additional IF clauses into your FREQUENCY Array to validate the Time

Four Arrays should not perform too badly even with the large number of precedents, however, I suspect you may be running these four Arrays for multiple dates simultaneously ? A matrix of Array results so to speak.
If that is indeed the case then sorting your data and/or avoiding Arrays altogether is certainly best way forward.

If you want to use VBA you can but whether or not it would be significantly quicker I couldn't say.

In part that would depend on :

a) how you wish to populate the result cells (individual UDF calls or generic Sub Routine to populate with Constants)

b) how the source data is altered / updated

c) how frequently the source data is altered / updated


On a final note...

If your data either is or can be sorted (by Date and Time) then you should find you can optimise the Arrays such that they are processing only the necessary precedents
(ie those rows associated to a given date rather than all rows)

I'm afraid I'm largely off line for the next week so I won't be able to assist personally, however, if you reply regards the above others will be able to wrap this up for you
(Given the above I've asked some others to "look in" as and when they have time to assist you if and where needed)

[jshot99]

Donkey - Thanks a ton for all of the help and pulling in others to help!

The data is not fully sorted and caused me to run into another problem. While looking at the date and time sort, it looked like everything was in perfect acsending order. I noticed that Column L (order number column) contains two types of order numbers; orders that start with 2 and orders that start with 4. I need to ignore those that start with 4and only count those that start with 2 (actually, only subtotal count those that start with 2).

So I know this adds even more on to this, but first I will answer all of the other questions. Also, I would rather use arrays if there is no difference in calculation speed. This way there is less for the user to worry about (i.e having to initiate the macro).

NO, these 4 arrays would only run for one date. The idea is to give the user a snap shot for that day, then there is a macro that copies and pastes the daily data snap-shot to a historical file. So again, no, it will only be for one date and 4 distinct time ranges. (ex: 4/4/11 - up to 11AM, 11AM-1PM, 1PM-330PM, and 330PM and later).

For VBA related questions:

a) Not sure what this means

b) The source data is pulled into another worksheet on the same workbook using data connections. All the equations and formulas pull off the data on that tab. This allows those that are very unexperienced with excel to update the source file when it changes title (every quarter the file changes name)

c) The source data is updated daily (by pressing the refresh data button), but the file name is changed quarterly

So yea NOW what I need this to do is everything stated before but only subtotaling numbers that begin with 2 in Column L. So count the subtotal of the orders that start with 2 in Column L based on date and time. The equation works for a single date and 4 set times (so 4 equations).

Sorry for adding more and more onto this, but this file is gonna save my **** in the long run!