Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
|
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 21st, 2015, 09:40 AM
Registered User
 
Join Date: Nov 2015
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Duplicate Date Sumarize and sum orders by qty parts ordered.

First off, I would like to thank you for writing the book, I purchased it about a month ago and it had helped me with my project I have been stuck on since August. I work in operations for a closet, cabinet manufacturing company. I have created a excel workbook that will track customer orders. I bring the data created from the excel order form into the workbook.

When I hit a command button it takes the data and moves a copy of it to the summary tab ( worksheet with all of the customer orders) as well as to the corresponding tab of the customer based on the value in column A (company name). However, each order has multiple products ordered for each order name . I would like to condense this into as few lines as possible, organized by the quantities of the total amount of each product type so I can change the jobs status from active to completed (status of each line item is in column M) I tried using the duplicate data codes found in the book but I cannot figure out how to get it to Add the quantities of the same product type for the same customer order. The Customer name is in column A, the job name is in column B, the product type is in column O and the quantities are in column H. Do you have any suggestions or ideas? A pivot table will not work because I also need to know the color of each job, order date, and due date as well as be able to change the status.

Thanks Ty
 
Old December 21st, 2015, 08:52 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Hello Ty - -

Thanks for buying my book and for your kind words about how it helped your project.

I read your message a few times and have a couple questions to make sure I understand the entire picture of what you are working with. Please look at the following bullet points and let me know if my understanding is correct, or if not what I am missing.

• In your workbook is a worksheet whose tab is named Summary.
• Somewhere in your workbook is a worksheet that you did not say the name of (doesn't matter) from which, when you click the Command Button, moves the data that is on that worksheet to two other worksheets.
• That unnamed worksheet holds just one record (the data you are moving) of an order at a time. No other data exists on that unnamed worksheet except a single record of an order, which might have many products on it but only one order that is all those products.
• The two worksheets that receive this single copied order are (1) the worksheet named Summary, and (2) another worksheet whose tab name is the exact same as the customer name in column A of that unnamed worksheet. For example, if the unnamed worksheet holding the single order is for a company called Drain Surgeons, then that second worksheet to receive the copied data has its tab name of Drain Surgeons.
• You want to condense an order into as few lines as possible. I assume you mean, into as few rows as possible, as opposed to non-data lines of text.

This is where I am unsure. Maybe you have multiple rows of identical products and their prices, and you want to subtotal the repeated products into one single line item per unique product designation of some kind, maybe by product type in column O.

In any case, I do not understand the relationship between just subtotaling each unique product type and how, by virtue of that, it allows you to change the job status from active to complete.

Also useful would be if the Summary and customer worksheets have running records of orders and if each copied order goes to the next available row, or do you skip a row to have a blank row between each record, and so on.

Finally, what happens to that singular order on the unnamed worksheet -- how is it laid out as it is created from the order form, is it deleted until the next order is imported (or however that next order get onto that unnamed worksheet), or maybe is there more than one order at a time on that unnamed worksheet.

This may be a question you might pose where you can include a screen shot of your before and after data, if my assumptions are off target and it would be easier to show your workbook's design than to describe it.

These are my initial thoughts to help you get the solution you need, depending on what you are working with and what your expected results are.

Tom
 
Old December 23rd, 2015, 09:19 AM
Registered User
 
Join Date: Nov 2015
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

• In your workbook is a worksheet whose tab is named Summary.
Yes, there is a worksheet in the workbook names “Summary”. The worksheets in the workbook are as followed.
1) “Home Page” (This just have hyperlinks to all the tabs in the workbook. I am using it as a navigation sheet.

2) “Import Sheet” (This is where I paste the order details from my excel order form that has all the job info and lines of code for each item ordered needed for the CNC machines.) when I click the command button on the page it adds a copy of this info to the summary tab.
3) The summary tab is the sheet in which I use to track all the items every ordered. Think this as a master summary sheet. No other data exists except a single record of an order, which might have many products on it but only one order that is all those products

4) A tab for each customer with the name of the company being the tab name.(the Value in column A).

Question: Maybe you have multiple rows of identical products and their prices, and you want to subtotal the repeated products into one single line item per unique product designation of some kind, maybe by product type in column O.

Answer: Yes, this is exactly what I would like. The purpose of this workbook is to track all of our orders so I know whether they are “active”, “done”, “billed” or on “hold”. Different parts are manufactured at different buildings or different locations in a building depending on the type of product they are. This being said, one type of product might be completed at this location but other parts from another location are still in production. Using a workbook would allow the foreman as well as myself to check the orders before they are picked up or delivered. I added conditional formatting so that when the order status changes the row color changes. I do not have totals in the workbook because I do not want the workers to see the totals of the jobs. The less they know the better. Totaling the products would make this task a lot easier because for each item ordered there is a row of code for that products.

For example,
If somebody ordered 27 drawer boxes, 12 drawer fronts and 37 closet (melamine parts). There would be 76 lines of code. That is a lot of changing the status of a job from active to done.
There are 6 different types of products. Thermofoil, vinyl (drawer boxes), Melamine, ¼” backing, moldings and counter tops.


In any case, I do not understand the relationship between just subtotaling each unique product type and how, by virtue of that, it allows you to change the job status from active to complete.

Question : Also useful would be if the Summary and customer worksheets have running records of orders and if each copied order goes to the next available row, or do you skip a row to have a blank row between each record, and so on.

Answer: The code on the import sheet (command button) adds the data to the next available row in the “Summary” sheet as well as each customer’s tab. However, I added the code in your book about organizing the data by each company and adding a space between company’s.

Question : Finally, what happens to that singular order on the unnamed worksheet -- how is it laid out as it is created from the order form, is it deleted until the next order is imported (or however that next order get onto that unnamed worksheet), or maybe is there more than one order at a time on that unnamed worksheet.

Answer: Layout BELOW: (These are the headings which begin in row 2 Starting with company at A2) The bold headings are the ones I would like to be on this new worksheet we are discussing the other info is just for references to look back on if there is a question about parts ordered
Company. Order Name. Order Date. Due Date. Location. Color. Edge band. Qty. Width. Height. Name. Notch. Status. Sq ft. Material. Material Type

Once I add data to the import sheet and click the command button and the data is moved to its respective location I either delete the data from the import sheet or just over write it with new data.


Lastly, I have screen shots but I cant figure out how to post them on this reply.
 
Old December 25th, 2015, 09:58 AM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Just thinking of a couple ideas, because the challenge is not seeing your Import worksheet layout of the actual data.

Have you tried to use the Subtotal method in VBA to programmatically sort the product types and subtotal them? Then you can condense the subtotal rows and loop through them (or copy their visible rows) to paste the subtotaled information to where you want to show that condensed synopsis for each product.

If that is not an applicable method to help answer your question, another idea is, because as you noticed, screen shot images are not posted on this particular forum, to perhaps ask this question about your project on an Excel forum that does accept images to help explain the subtleties of your worksheet. That will provide a better understanding of what you are up against.

On the face of it from your description if I still understand it correctly, you can start by copying your Import sheet to a helper sheet and subtotaling the data that way, which leaves the original Import sheet untouched. The macro recorder can help you with the syntax of the Subtotal method and the collapsing of rows to only show their Subtotal amounts. This piece of the project all seems do-able in code from the sounds of it. The book's examples, along with sites on the web for code snippet examples, plus the macro recorder, along with forums to show your sheet images if need be, are resources that can collectively help solve this.

Tom

Last edited by Tom Urtis; December 25th, 2015 at 10:01 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert XML to Word Ordered List? kvbhaskar7 XSLT 0 November 14th, 2011 04:20 PM
Error in Saving Ordered Items newbie_inasp2.0 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 6 August 22nd, 2007 05:11 PM
SELECT list re-ordered stuart.pinfold Classic ASP Basics 0 January 24th, 2007 02:36 PM
Append String to an ordered list item mat41 HTML Code Clinic 8 October 8th, 2004 09:25 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.