Excel Macro Question

Hey, question about excel.

Okay, let's say there's an excel spreadsheet...on te spreadsheet are the orders from different restaurants. Multiple orders...meaning the restaurants are displayed more than once...and then the amount for each order is displayed in a different column.

Now, I need to know if it's possible to create a macro that separates the restaurants alphabetically, SPLITS them up as to (per restaurant), and then totals the amount of money for each restaurant.

so if you have

Res 1, $5
Res 1, $10
Res 1, $20
Res 2, $7
Res 2, $29
Res 2, $100


It would need to be split up as to say

Total for "Res 1" = $35
Total for "Res 2" = $136

Would it be possible to do that with a massive list?

If so, how?

Thank you.

Yeah, that would be fairly trivial to do.

Is this a homework assignment?

No, business.

thanks

This reminds me why I hate Visual Basic...

Anyway, here's a quick and dirty example that seems to work. I'm no Excel guru so for all I know there is a built-in worksheet function to do this, but the following macro was easy enough to write (once I got around VBA's quirkiness compared to other languages).

Given this

http//www.morrisonline.us/pics/excel-tfoaf.png[" alt=""/imga841f115dd]

The totals part on the right will be generated from the list on the left by this macro

[code1a841f115dd]Sub RestaurantSummary()
' Assume restaurant list is on first worksheet, with restaurants starting at A1 and dollar amounts at B1.
' Totals will be listed starting at E1.

Worksheets(1).Activateurl==http://=http:///url
Dim inRange, outRange

DataIn = "A1" ' start of restaurant input data
DataOut = "E1" ' start of subtotal output area

Set inRange = Range(DataIn)
Set outRange = Range(DataOut)

' sort input range
inRange.Sort Key1:=inRange, Key2:=inRange.Offset(0, 1)

' prep output range
outRange.CurrentRegion.Clear
outRange.Value = "Totals:"
Set outRange = outRange.Offset(1, 0)

' loop through all input data and output results as restaurant changes
lastRest = inRange.Value
lastTotal = 0
For Each restaurant In inRange.CurrentRegion.Columns(1).Cellsurl==http://=http:///url
If restaurant.Value <> lastRest Then
outRange.Value = lastRest
outRange.Offset(0, 1).Valueurl==http://=http:///url = lastTotal
Set outRange = outRange.Offset(1, 0)
lastTotal = 0
lastRest = restaurant.Value
End If
' Add value in column adjacent to restaurant name
' (edit offset if your input range is different)
lastTotal = lastTotal + restaurant.Offset(0, 1).Valueurl==http://=http:///url
Next

' output last one
If (lastRest <> "") Then
outRange.Value = lastRest
outRange.Offset(0, 1).Valueurl==http://=http:///url = lastTotal
End If

End Sub[/code1a841f115dd]

You can add a macro module to your spreadsheet and paste that in, or you can [url==http//www.morrisonline.us/tfoaf-restaurant.xls]download my example file which includes the macro[=http//www.morrisonline.us/tfoaf-restaurant.xls]download my example file which includes the macro[/url] and paste your data into it (make sure your Office security setting allows macros, and you'll probably get a macro security warning unless you've disabled macro security).

Note that the macro assumes the restaurant names start at A1 and their corresponding dollar amounts start in B1. You'll have to edit the macro code to change that. Also note the totals are output starting at E1, which you can also change (or send to another worksheet if you prefer).

The restaurant list doesn't have to be pre-sorted, but [ba841f115dd]it will be sorted by this macro,[/ba841f115dd] so make sure that's okay. Copy it to another worksheet if you need to keep the original order. Also note that there can be no gaps (empty rows) in the restaurant list, because the macro uses CurrentRegion as a shortcut, which stops at the first blank row in the range. It could be coded to actually keep looking past a gap, but I'm not going that far.

Standard disclaimers apply I take no responsibility for bugs or whatnot.

Heheh. I'll let my partners know and see how this works out. Thank you very much.

Oh, and I hope this doesn't bug you, but is it possible, because I forgot to tell you this, is it possible for you to include in this macro for each restaurant total it outputs, it also outputs 5% of that total, and label it, "Amount we receive" or something of that nature.

Thanks you!!

[quotef0697e87f4="TFOAF"]
Oh, and I hope this doesn't bug you, but is it possible, because I forgot to tell you this, is it possible for you to include in this macro for each restaurant total it outputs, it also outputs 5% of that total, and label it, "Amount we receive" or something of that nature.
[/quotef0697e87f4]
Oh come on... After all he did for you already, you can't do a simple =CELL WITH TOTALli0.05 by yourself for this?

[quote42c56fb2f0="hehehhehe"][quote42c56fb2f0="TFOAF"]
Oh, and I hope this doesn't bug you, but is it possible, because I forgot to tell you this, is it possible for you to include in this macro for each restaurant total it outputs, it also outputs 5% of that total, and label it, "Amount we receive" or something of that nature.
[/quote42c56fb2f0]
Oh come on... After all he did for you already, you can't do a simple =CELL WITH TOTALli0.05 by yourself for this?[/quote42c56fb2f0]
Hey...might as well use my resource to maximize my utility. )

Naw, I just don't wanna screw it up.

You can either enter static formulas on the worksheet to reference the total cells, or you can add the line

[code1f2d483de76]outRange.Offset(0, 2).Valueurl==http://=http:///url = lastTotal li 0.05[/code1f2d483de76]

After the line

[code1f2d483de76]outRange.Offset(0, 1).Valueurl==http://=http:///url = lastTotal[/code1f2d483de76]

to put the 5% in the adjacent column. You'll have to work out where to put the caption, formatting, etc. from there.

David, could you PM me your email address.

I would like to email you the spreadsheet that I have, and you can implement the Macro into it, or create a macro file or something, and show you what it looks like. When I ran the macro it turned everything blank.

I'm running Excel 2007.

Thank you.

Is there some sort of class or tutorial I can take part in that teaches the basics of all the crap you guys are always talking about? The HTML, the coding, all this crazy stuff?

I want to get a little more computer efficient, but I don't want to have to do something like make it my major and go to school for it and do all this crazy stuff. Any Ideas?

Sorry Foaf, I kind of just highjacked you unintentionally but I figured it'd be okay since you kind of ended it where you did D

Would it be easier just to let dmo run the business?

[quoteb759d9c482="CollidgeGraduit"]Would it be easier just to let dmo run the business?[/quoteb759d9c482]
lol, no. The spreadsheet ...it just simplifies some way we can get totals in the business. It just makes it easier to use rather than doing it manually. P

foaf - he fricking posted a damn spreadsheet with the macro installed. I'm not sure how much more you could expect him to do.

[quotec864a57b9f="TFOAF"]Hey...might as well use my resource to maximize my utility. )

Naw, I just don't wanna screw it up.[/quotec864a57b9f]
I hope you're not calling dmo your "resource."

And foafy, people usually get paid for what dmo has already done for you, especially when it's for a business. You don't think you can put forth the effort to implement what he has already coded for you? All you have to do is do is create cells next to each restaurant and do a =CELLli0.05 for each restaurant.

A foafy post in t-t-t-tech usually ends up with dmo helping him big time, and foafy asking for more and more, with dmo displaying his infinite patience.

No, lol, the thing is that the columns for the totals and the name of the restaurant are not next to each other.

The names of the restaurants are in Column D, and the amount of monies are in Column K.

Also, there is a title for each column, so basically D1 is "Names" and K1 is "amount of monies".

That's why I'm just confused, I was never really experienced with a Macro.

And I do appreciate what David did for me. Just the current code for the Macro doesn't work because of the way the spreadsheet is set up.

Please take a look
[img89e5ef5213]http/" alt=""/img231.imageshack.us/img="231/6619/resdc9.th.jpg[" alt=""/img89e5ef5213][=http//img="231.imageshack.us/my.php?image=resdc9.jpg][img89e5ef5213]http/" alt=""/img231.imageshack.us/img="231/6619/resdc9.th.jpg[" alt=""/img89e5ef5213]

Well, I gave you what you asked for -- you only mentioned two columns in your original post, so my macro took that simplistic approach.

It could be modified to work with the more complicated spreadsheet, you just can't use the CurrentRegion shortcut on the full worksheet. There are several approaches you could take, including the quick & dirty approach of copying the restaurant and money columns to adjacent columns on a another worksheet and running my macro there. The problem with running the macro on the full worksheet is that it requires a sort by restaurant, and that worksheet is sorted by date. You could sort again by date after it was finished, if you really wanted to do that. But copying the two columns to another worksheet would certainly be the easiest approach.

Honestly since this is for a business, I don't code for free. I thought I was just helping you out with a project -- I'm not going to spend an hour or two getting this just right and give it away for free for use by a business.

If they want to pay me for my time at $65/hr then I'll be happy to provide a macro to your specs. Otherwise, feel free to take the code I already gave you and make it work.

http//farm1.static.flickr.com/119/294168009_b25decaddf.jpg[" alt=""/imgda2a58d800]

Is it possible to move a column in Excel? Like move D next to A

Of course. To do it manually from Excel (rather than from a macro), right click on the column header you want to move, and click Cut. Then right click on the column header immediately to the [b9b287f357a]right[/b9b287f357a] of where you want it moved to, and select "Insert Cut Cells." If you get it wrong, just Ctrl-Z/Undo and try again.

Mind you if you do this with the macro as is, you'll need to change the input and output ranges (A1 and E1 in my example) but more importantly you'll likely wind up with those columns sorted inside the rest of the spreadsheet -- so you'd need to code it to re-sort itself after the macro is finished totaling things up.

[b9b287f357a]EDIT[/b9b287f357a] Actually, the resort within the spreadsheet wouldn't work because there is no "order" to the restaurant names in the original worksheet. You could either modify the macro's sort, or just sort manually from Excel, to sort the [i9b287f357a]entire[/i9b287f357a] worksheet by the restaurant name column. Then you could sort it back after the macro does it's thing. This could easily be added to the macro.

Well, if I move the columns into the columns A and B, the macro you made should work, right?

[quotee1d4bc7371="TFOAF"]Well, if I move the columns into the columns A and B, the macro you made should work, right?[/quotee1d4bc7371]
Read the edit I just made ^^^

Yes it will work as long as the [ie1d4bc7371]entire[/ie1d4bc7371] worksheet is sorted, not just those columns. You'll have to edit the macro to place the Totals output somewhere else, though. And since the output area is cleared, if you don't have a blank columns on both sides of the output column, it will probably select and clear your whole worksheet. ) Again, this could be fixed in the macro code.

[be1d4bc7371]EDIT[/be1d4bc7371] Oh, and one more thing -- you'll have to insert a blank column after the dollar amount, again because the CurrentRegion function looks for breaks between cells.

there is not a word to describe the patience you're exhibiting dMo.

It has it's limits. Trust me.

Thank you for your time and patience. I really appreciate it David.

So if I sort the restaurants alphabatically in the first column, how do I make the second column coincide with the rows that it once was together with.

Because otherwise Restaurant B might show up with a money from Restaurant A since the second column wouldn't be sorted with the first one. Or is there a way to sort the first one alphabetically and have the totals also sort along with the first column. I know that sounds really confusing...Basically the data from A and B have to match after alphabetical sorting.

Be honest, (and I'm not being a dick) do you have ANY experience with excel? Your most recent question is just about one of the first things you learn when you're using excel.

If you click on Column A, and then click the sort button, or do an advanced sort through the toolbar menu, you're prompted and asked whether you want to 'expand the selection' (or something like that) or whether you want to continue without expanding. In your case, assuming that column B is matched to column A, then yes, you want to expand the selection.

Of course, this supposes that there's not data in column c-infinity (either you don't care if it's sorted, or it's not there). If there is data beyond column B, then select columns A and B together, and don't expand the selection.

The macro sort handles the adjacent rows automatically. If you sort manually from within Excel, you select both columns and tell it which columns to sort on.

Note my other edit above if you do it this way (by moving both columns to A1 & B1), column C must be blank. So just insert a blank column at C.

my respect for dmorris just shot through the roof.

[quote1c1e702aed="doylnea"]Be honest, (and I'm not being a dick) do you have ANY experience with excel? Your most recent question is just about one of the first things you learn when you're using excel.

If you click on Column A, and then click the sort button, or do an advanced sort through the toolbar menu, you're prompted and asked whether you want to 'expand the selection' (or something like that) or whether you want to continue without expanding. In your case, assuming that column B is matched to column A, then yes, you want to expand the selection.

Of course, this supposes that there's not data in column c-infinity (either you don't care if it's sorted, or it's not there). If there is data beyond column B, then select columns A and B together, and don't expand the selection.[/quote1c1e702aed]
Yes, I am fairly experienced with Excel, not so much macros. I just haven't used Excel in a really, really long time.
[quote1c1e702aed="dmorris68"]The macro sort handles the adjacent rows automatically. If you sort manually from within Excel, you select both columns and tell it which columns to sort on.

Note my other edit above if you do it this way (by moving both columns to A1 & B1), column C must be blank. So just insert a blank column at C.[/quote1c1e702aed]
Alright, so if I delete all of the columns, and then move the other ones to A1 and B1, then the Macro should work? (because the other columns are not relevent to anything when figuring out these totals.)

[quotebcdd685039="TFOAF"]Alright, so if I delete all of the columns, and then move the other ones to A1 and B1, then the Macro should work? (because the other columns are not relevent to anything when figuring out these totals.)[/quotebcdd685039]
Yes. If you move the restaurant list to column A and the money list to column B, and delete everything else, it should work fine without any changes.

To preserve the original spreadsheet, if I were you I would do it on a clean workbook (the tabs at the bottom). Just make a change to the macro to select workbook #2 if you put it on the second tab. Or if you want you could just remove the Workbook.Activate(1) line entirely, in which case it should run on whatever the currently selected workbook is.

That way you have your original data untouched on worksheet 1, and the subtotals data on worksheet 2.

[quote8733572c90="dmorris68"][quote8733572c90="TFOAF"]Alright, so if I delete all of the columns, and then move the other ones to A1 and B1, then the Macro should work? (because the other columns are not relevent to anything when figuring out these totals.)[/quote8733572c90]
Yes. If you move the restaurant list to column A and the money list to column B, and delete everything else, it should work fine without any changes.

To preserve the original spreadsheet, if I were you I would do it on a clean workbook (the tabs at the bottom). Just make a change to the macro to select workbook #2 if you put it on the second tab. Or if you want you could just remove the Workbook.Activate(1) line entirely, in which case it should run on whatever the currently selected workbook is.

That way you have your original data untouched on worksheet 1, and the subtotals data on worksheet 2.[/quote8733572c90]
Oh, it's alright. I can always re-export the worksheet, because it's an export of data from a database. ;)

Thanks so much for your help.

You asked how to move a column in Excel, I would say your skill base is zero to none.

[quotef2672277d5="TryinToGetPaid"]You asked how to move a column in Excel, I would say your skill base is zero to none.[/quotef2672277d5]
I figured it out. I was brain-dead yesterday...I had no sleep for 36 hours. I knew how to do it...just not yesterday, lol.

And the cells were merged so it wouldn't let me cut and paste columns, which confused me yesterday...but not today. )