Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
I am fairly new to excel. I use it to do relatively basic formulas and charts, but I am not very good with Macros and VB. I have been asked to help make out reports more readable. Basically we have a query that dumps a bunch of info into an excel spread sheet. I then have to sort it and delete some columns we don't need. up untill this point i am fine making a macro. but after that i get stumped. basically we have a list of items that were invoiced, so there are rows that have the same invoice number, we sort them by invoice number so we can see the group of items on that order. What my boss would like, is for these separate invoices to be grouped two lines inserted and a total sales sumed up (which i know how to do), basically my question is, is there a way to run through a column of data useing vb and find unique values and then enter a new line and preform a task. I am used to doing most of my stuff in php so this is kinda what i was thinking loop(runs through each row in a column, or data range b2:b230) { $lastnumber=someinvoicenumber //the last unique one $newnumber=currentCellValue //current cell value if ($newnumber=$lastnumber) //checks if they match { $count=$count+1 //if they match add to the counter } else //they don't match, start routine { insert two rows add sum field into a cell on that row, the sum would go from that cell to that cell - $count $lastnumber = $newnumber } endif; so basically i don't know how to write that in vb. I hope i explained my self somewhat okay. Any help would be greatly appreciated!!!!! -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
Can I ask some questions?
Talking about "a query" is that MS Query? Yes: You can do sorting and selection and column deletion in MS Query. So when the data arrives it is already sorted and filtered. No: What is it? Is it a .csv file import? Your task is: sort all rows by invoice number and item for every invoice number you want to add two lines and create a total of all items on the invoice. What are the 2 lines for? Readibility? Is it 2 lines below the invoice or 1 above and 1 below? Does that summarize your problem? Hans |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
hans, First off, thanks for the quick reply. okay a little background, I know php and mysql, so most of the stuff i do is moving client to server to client. I now just started working for a company that uses access and great plains. I have yet to meet with the Database Admin, so i do not know the table structure or query structure yet. Once I get that down, i will start writing the queries myself. untill then i am forced to use the access program that everyone else has. in there you can pick an open order report and then it will send it to excel, (i don't know how, like i said i don't work with microsoft products much). the excel spreadsheet then has about 15 columns with various data in them for each item in order to make the report more readable, we sort based on the req ship date and then by order number. we also delete some of the columns that we don't needs (this is why i want to set up my own queries, because you can see how inefficient this is). anyway, so I set up a macro that pretty much does that, and adds a title and date, so we can forward the report to others. the problem is that there is no break from one group or orders to the next, so it is hard to see a summary. I have been going through it by hand and inserting two blank rows in between each group of orders so it is easier to read. I also add a sum function to get the total sale from the group of each unique order number. after doing about 200 records i was fed up and thought there had to be a way to make a macro do this, but i don't know how to set it up so it looks for unique value groups and preform a task at the end of the group. i hope that clearifies it somewhat. like i said, if they had php running on the server i could pull up a webpage that would look nifty, but unfortunately they do not, and i find VB very confusing, but then again i never really use it. Thanks for the help!!! travis -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
Hi Travis,
I've tried to come up with a quick solution for what I seem to understand is your request. Check it and if it is what you want we can always make it nice and neat. This is the "query" as you get it: req ship date orderno. itemno. qty amount 01.05.2006 14510 1234 50 150,00 01.05.2006 14510 2345 70 178,00 01.05.2006 14510 4221 35 98,00 01.05.2006 16422 7552 115 485,00 01.05.2006 16422 6221 78 123,00 01.05.2006 18532 1122 114 253,00 01.05.2006 18532 1422 119 193,00 10.05.2006 16422 1558 20 452,00 10.05.2006 19741 4444 42 79,00 10.05.2006 19741 5555 115 452,00 10.05.2006 21020 1888 30 78,00 22.05.2006 18010 2445 45 70,00 22.05.2006 18010 2446 121 225,00 And this is the result of the proposed procedu req ship date orderno. itemno. qty amount 01.05.2006 14510 1234 50 150,00 01.05.2006 14510 2345 70 178,00 01.05.2006 14510 4221 35 98,00 Order Total 426,00 01.05.2006 16422 7552 115 485,00 01.05.2006 16422 6221 78 123,00 Order Total 608,00 01.05.2006 18532 1122 114 253,00 01.05.2006 18532 1422 119 193,00 Order Total 446,00 Group Total 1480,00 10.05.2006 16422 1558 20 452,00 Order Total 452,00 10.05.2006 19741 4444 42 79,00 10.05.2006 19741 5555 115 452,00 Order Total 531,00 10.05.2006 21020 1888 30 78,00 Order Total 78,00 Group Total 1061,00 22.05.2006 18010 2445 45 70,00 22.05.2006 18010 2446 121 225,00 Order Total 295,00 Group Total 295,00 And this is the procedure that does this: sheetname = ActiveSheet.Name GroupCol = 1 'column that holds the group OrderCol = 2 'Column that holds the order ValueCol = 5 ' column to sum RepStartRow = 2 'row number where the report data starts RowsEndOfRep = 4 'How many empty define the end of the report GroupStartCol = 0 'first row of current group GroupEndCol = 0 'last row of current group currgroupkey = "" 'key of current group currorderkey = "" 'key of current order prevgroupkey = "" 'key of previous group prevorderkey = "" 'key of previous order endofreport = False 'signal "end of report" NumRowsGroup = 0 'number of rows in current group NumRowsOrder = 0 'number of rows in current order currentrow = RepStartRow 'current row number sumorder = 0 'sum of current order sumgroup = 0 'sum of current group If prevgroupkey = "" Then prevgroupkey = Worksheets(sheetname).Cells(RepStartRow, GroupCol) prevorderkey = Worksheets(sheetname).Cells(RepStartRow, OrderCol) End If While endofreport = False Do If Worksheets(sheetname).Cells(currentrow, GroupCol).Value = "" And _ Worksheets(sheetname).Cells(currentrow + 1, GroupCol).Value = "" And _ Worksheets(sheetname).Cells(currentrow + 2, GroupCol).Value = "" And _ Worksheets(sheetname).Cells(currentrow + 3, GroupCol).Value = "" Then endofreport = True End If If endofreport = False And Worksheets(sheetname).Cells(currentrow, GroupCol).Value = "" Then MsgBox ("Already done") endofreport = True Else NumRowsGroup = 0 If Worksheets(sheetname).Cells(currentrow, GroupCol).Value = prevgroupkey Then NumRowsGroup = NumRowsGroup + 1 If Worksheets(sheetname).Cells(currentrow, OrderCol).Value = prevorderkey Then NumRowsOrder = NumRowsOrder + 1 sumorder = sumorder + Worksheets(sheetname).Cells(currentrow, ValueCol).Value sumgroup = sumgroup + Worksheets(sheetname).Cells(currentrow, ValueCol).Value Else Worksheets(sheetname).Rows(currentrow).Insert Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Value = "Order Total" Worksheets(sheetname).Cells(currentrow, ValueCol).Value = sumorder sumorder = 0 prevorderkey = Worksheets(sheetname).Cells(currentrow + 1, OrderCol).Value 'put in sum fucntion End If Else Worksheets(sheetname).Rows(currentrow).Insert Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Value = "Order Total" Worksheets(sheetname).Cells(currentrow, ValueCol).Value = sumorder sumorder = 0 prevorderkey = Worksheets(sheetname).Cells(currentrow + 1, OrderCol).Value Worksheets(sheetname).Rows(currentrow + 1).Insert Worksheets(sheetname).Cells(currentrow + 1, ValueCol - 1).Value = "Group Total" Worksheets(sheetname).Cells(currentrow + 1, ValueCol).Value = sumgroup sumgroup = 0 prevgroupkey = Worksheets(sheetname).Cells(currentrow + 2, GroupCol).Value 'put in sum fucntion currentrow = currentrow + 1 End If currentrow = currentrow + 1 End If Loop Until endofreport Wend These are the assumptions/actions: If there are 4 consecutive empty rows it is assumed to be the end of the report If an empty row is found it is assumed that the procedure has already been run Hans |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
Hi
Take a look at the built in Subtotal function in Excel, found under DataSubtotals. Also, look at Group and Outline under the same menu and see if these will help. -- Regards Roger Govier "trav" wrote in message ... hans, First off, thanks for the quick reply. okay a little background, I know php and mysql, so most of the stuff i do is moving client to server to client. I now just started working for a company that uses access and great plains. I have yet to meet with the Database Admin, so i do not know the table structure or query structure yet. Once I get that down, i will start writing the queries myself. untill then i am forced to use the access program that everyone else has. in there you can pick an open order report and then it will send it to excel, (i don't know how, like i said i don't work with microsoft products much). the excel spreadsheet then has about 15 columns with various data in them for each item in order to make the report more readable, we sort based on the req ship date and then by order number. we also delete some of the columns that we don't needs (this is why i want to set up my own queries, because you can see how inefficient this is). anyway, so I set up a macro that pretty much does that, and adds a title and date, so we can forward the report to others. the problem is that there is no break from one group or orders to the next, so it is hard to see a summary. I have been going through it by hand and inserting two blank rows in between each group of orders so it is easier to read. I also add a sum function to get the total sale from the group of each unique order number. after doing about 200 records i was fed up and thought there had to be a way to make a macro do this, but i don't know how to set it up so it looks for unique value groups and preform a task at the end of the group. i hope that clearifies it somewhat. like i said, if they had php running on the server i could pull up a webpage that would look nifty, but unfortunately they do not, and i find VB very confusing, but then again i never really use it. Thanks for the help!!! travis -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
THANK YOU SO MUCH !!!!!! it works GREAT. you have no idea how much easier you made my life. THANK YOU Travis -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
okay, so it works great, i am making a few changes to it so that it makes it a little easier to read. Mainly i just want to bold and add a line to the top to the cell that the order total is in. I know nothing about VB, but i guess it would be something like this. Code: -------------------- Worksheets(sheetname).Rows(currentrow).Insert Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Value = "Order Total" Worksheets(sheetname).Cells(currentrow, ValueCol).Value = sumorder Worksheets(sheetname).Cells(currentrow, ValueCol - 1).Font.Bold = True Worksheets(sheetname).Cells(currentrow, ValueCol).Font.Bold = True -------------------- When i tried that, i recieve no errors but the cells aren't bolded. and i have no idea how to format a line to the top of the cell. any suggestions. also, do you know of any place i can go to find a quick reference guide to VB syntax Thanks Trav -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
okay i figured out the bolding, i think i must have had the code in the wrong place. but i still don't really know how to add cell formating to the added cells for lines above and below -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
Hi Trav,
Cells(currentrow, ValueCol). Currentrow represnts the row number and ValueCol represents the column number for the cell you want to apply the formatting to. So, Cells(currentrow-1, ValueCol) is the row above Cells(currentrow+1, ValueCol) is the row below Similarly Cells(currentrow, ValueCol-1) is the cell the left Cells(currentrow, ValueCol+1) is the cell to the right If you want to apply the formatting to the entire row: Worksheets(sheetname).Cells(currentrow, ValueCol).entirerow.Font.Bold = True whereby the cell coordinates don't matter as long as they are in the row you want to format. Btw, you asked for a book about VBA. Unfortunately there is no "easy" way to VBA as far as I know. They all want to explain the "Object" concept in the first three quarters of the book and usually I get fed up after 50 pages. I would appreciate a lot of well explained examples. that would help me personally much more. Just try the help. There are many good examples that you can take and modify. Hans |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
thank you so much hans you have really helped me with my understanding of excel. i have been playing around and i am much more comfortable now with formating and useing data in the different cells. two last question though: is there a way to convert from a column letter to column number, or header. that why i can make a form and the user can pick what columns he/she wants to total, or sort by. i am asking not only for this app, but also because i have made another and i like passing the col variables so that i can make it as dynamic as possible, if there was a way to convert from column E to 5, that would make my life easier also (2nd question), when using the syntax worksheet(sheetname).cell(row, column).font.bold how can i make that a selection of cells, say column and (column + 1) so instead of having to do this ... Code: -------------------- worksheet(sheetname).cell(row, column).font.bold = true worksheet(sheetname).cell(row, column + 1).font.bold = true -------------------- i can condense it into one statement. thank you so much for your help -- trav ------------------------------------------------------------------------ trav's Profile: http://www.excelforum.com/member.php...o&userid=31420 View this thread: http://www.excelforum.com/showthread...hreadid=511138 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
group by report
Hi Trav,
Here's the answers: 'this is for taking a value from a user specified cell and put it somewhere else ' r = Range("e1").Value '<-- this is where your user has entered the cell reference Cells(1, 1).Value = Range(r).Value 'cell(r,c) indicates where to put the value 'This lets a user specify a column to sort r = Range("e1").Value '<-- this is where your user has entered the column letter Range("A1:A7").Sort _ Key1:=Columns(r) 'This lets a user specify a cell to use that column as sort key r = Range("e1").Value '<-- this is where your user has entered the column letter Range("A1:A7").Sort _ Key1:=Range(r) 'This lets a user specify a range to sum up sr = Range("e1").Value er = Range("f1").Value Set r = Range(sr + ":" + er) tot = 0 With r For Each c In r tot = tot + c.Value Next c Range("C6").Value = tot End With 'This sets "bold" for all cells in the range (1,1) to (10,4) Range(Cells(1, 1), Cells(10, 4)).Font.Bold = True Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table to calc by group | Excel Discussion (Misc queries) | |||
Calculate cell row and column | Excel Discussion (Misc queries) | |||
Need help w/ Weight Formula | Excel Discussion (Misc queries) | |||
Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |