Summing undetermined colums

  • Thread starter Thread starter RJH
  • Start date Start date
R

RJH

Hello,
You guys helped me with a problem a week or so ago. Your solution worked
great!
Here's another, if you don't mind.

My stockroom guy is using Excel to track orders. He lists his orders
horizontally across the row and grouped by order number.
He may have 1, 2, or many lines in a order. He leaves one blank row to
differentiate between groups of orders. We have a formula giving him the
total price of each line and he uses autosum to get a total of the column of
totals in a full order. Although the autosum feature is great, is there a
way to automate his full order total. I believe it would involve finding
the blank row above the order to determine to so called "top"and summing all
the lines below that.

Thanks for any ideas!

RJH
 
I recommend fiddling with Pivot Tables / Pivot Reports. This will do the
sum, group, grandtotal that you're after.
 
Hi,

Maybe it 's the way some people use Excel.
If you would just leave all orders togheter, no blanks.
A simple sort on order number and add a subtotal (Menu Data/Subtotal ...)
and Voila.
Because we don't get a proper course (or get a book), we don't use certain
software as we should.
Formatting Data is the End report thing.
Regards,

Jean-Yves Tfelt
 
Though a pivot table may seem great, and yeah perhaps all the data should be grouped together on a single worksheet, the overall process is not broken to the point of needing a complete revamp. Besides each of the other two fixes would require writing more code and performing more searches and still be limited, and ultimately not the way that the the customer is looking for. And actually, it almost sounds like this should all be done in Access rather than Excel, but once the company has committed to a particular means of working, it can be difficult to change it unless required by someone of higher authority/there's a big problem.

Here is an idea that can be done keeping your existing formatting.

If the "total" that you are looking for is always in the same position in relation to the numbers being totalled then you can write some VB code to sum up the items in your "group"

As I understand you have groups of data like the following:

Company1 Item1 $4.51
Company1 Item2 $5.23
Company1 Item3 $20.63

Company2 Item1 $6.52
Company2 Item2 $8.02
Company2 Item3 $10.41
Company2 Item4 $11.06

And say you want to add the "Total" just below each of the company groupings, then you know that all items to be totaled are located "above" the cell used to hold the total. The total cell could be anywhere in the row, but as long as you (the Programmer) knows where the last piece of data to be summed starts, then you can do the necessary addition.

The routine you would want would simply start at the last item to be summed, and work "up" until a blank space is found. The result then is the sum of all items for that group.
 
I am a little confued on how the data is layed out on the
sheet. Could you give us a small example?
 
You have hit the nail on the head in both instances. Our accounting dept.
had decided that Excel is what we would use and they set up this layout.
It's up to us to make it work as best we can. I'm just trying to make it
easier for the guys to use. The layout you have "laid out" here is exactly
the way we do it. I understand what your saying about summing "up" to a
blank. My problem is that none of us have any training on Excel. I've been
reading about and writing some of the macros we are now using but I'm lost
as how to detect a black cell in order to determine the amount of lines to
be summed. How would I do this?

Thanks again.

RJH


COM said:
Though a pivot table may seem great, and yeah perhaps all the data should
Click to expand...
be grouped together on a single worksheet, the overall process is not broken
to the point of needing a complete revamp. Besides each of the other two
fixes would require writing more code and performing more searches and still
be limited, and ultimately not the way that the the customer is looking for.
And actually, it almost sounds like this should all be done in Access rather
than Excel, but once the company has committed to a particular means of
working, it can be difficult to change it unless required by someone of
higher authority/there's a big problem.
Here is an idea that can be done keeping your existing formatting.

If the "total" that you are looking for is always in the same position in
Click to expand...
relation to the numbers being totalled then you can write some VB code to
sum up the items in your "group"
As I understand you have groups of data like the following:

Company1 Item1 $4.51
Company1 Item2 $5.23
Company1 Item3 $20.63

Company2 Item1 $6.52
Company2 Item2 $8.02
Company2 Item3 $10.41
Company2 Item4 $11.06

And say you want to add the "Total" just below each of the company
Click to expand...
groupings, then you know that all items to be totaled are located "above"
the cell used to hold the total. The total cell could be anywhere in the
row, but as long as you (the Programmer) knows where the last piece of data
to be summed starts, then you can do the necessary addition.
The routine you would want would simply start at the last item to be
Click to expand...
summed, and work "up" until a blank space is found. The result then is the
sum of all items for that group.
 
I may have been a little short sited too... If you would rather do the totalling on a separate sheet, or on a command of a button, then the problem would be easier to solve... Given that... Might be able to produce something that would work, though the way I would do it might be a little messy.

First thing would be to set up a sheet (Companysheet) that will get updated with each "company" title, as it is pulled from your stockroom guy's current spreadsheet, and the total for that "company". After each "grouping" for a company, you would have your total field, which would reference the total on the Companysheet. (I.e. "=Companysheet!B1")

Now, when anything is updated on your currently existing sheet, VBA code in the background, figures out what companies exist in your existing sheet. Stores those results in the Companysheet, by inserting or deleting companies as necessary, maintaining an alphabetically sorted list.

The total for each company is calculated and inserted in column B for the company in question on the CompanySheet, as the sheet is parsed. Ie. finds that Company1 exists... Ensures Company1 is in the CompanySheet. Then totals all values for Company1 and stores the result. Then because the "Total" Cell for Company1 is referenced as CompanySheet!B1, the total is up-to-date.

So we would need to use the following event to "catch" the change..

Under ThisWorkbook the SheetChange event.

This event would call the module/code to start determining the companies and the totals.

Given that the first company starts say at Cell A2, then one would need to know in what column the company name, and in what column the Order price amount is located.

while current cell <> ""
Companies row = insert CompanyName(CompaniesName)
While the Current Cell is <> ""
Total = Total + CurrentRow's Amount
Move down one row
wend

Set the total for the Company on the CompanySheet

if the row on the currently existing spreadsheet does not have a total, then create the total and the reference to it.

Move down 2 rows 'One row is a blank, and the following row should be a company if one exists.
wend

This will have updated each company..

The insertcompany name feature would be rather simple.. Look in Column A, if the company is not found, then move down either till the end of the list, or the company to insert is before the next "bigger" and after the next "smaller" company. If the inserted company is less than the first, insert at the beginning. Progress through each company until the next company is smaller than the inserting company. If reach the end of the list, insert at the end.
If the company is in the list or is added to the list, then return the row of the new company.

That's a way that this could be accomplished, (though in pseudo code.) I might have some time tomorrow to refer back to this, and actually create it for you, or someone else might be able to do it.. :) Good luck, again sorry..
 
Back
Top