HOW TO: Generate Summary 1

[stillinlife101]

Worksheet A Columns: Quantity, Part #, Description Line 1, Description Line 2, Unit Price, Total Price

The "part #" column is already filled in with part numbers, and the unit price already has the prices. The total price calculates itself as Quantity * Unit Price.

The quantities are all set at 0. When I set the quantity to a number greater than zero, I went it to send that part number's information to add to a quote. So, here's a model of Worksheet A:

Quantity Part# Desc.Line1 Desc.Line2 U.Price T.Price
0 pn1 desc1 desc2 1.24 0.00
2 pn2 desc1 desc2 5.00 10.00
0 pn3 desc1 desc2 3.76 0.00
3 pn4 desc1 desc2 1.25 3.75
1 pn5 desc1 desc2 9.14 9.14

I want this to generate the following output in worksheet B:

Item Quantity Part# Description U.Price T.Price
1 2 pn2 desc1 5.00 10.00
desc2
2 3 pn4 desc1 1.25 3.75
desc2
3 1 pn5 desc1 9.14 9.14
desc2
Total: 22.89

Note that all the parts that have an order quantity greater than zero have been included in Worksheet B, but there are no zero quantity entries. If the total number of items exceeds a certain number (i.e. the number that would fill up a page), I also want to be able to create a new header beneath the first page so that it prints as a quote.

If you have any questions, please ask. I already tried posting this question, but I don't think I was clear enough about the problem. Thanks!

Dan

 

[mintjulep]

This may be a bit messy, but should work. I don't have excel on the machine I'm on at the moment.

Create two helper columns between your existing Quantity and Part# columns on sheet A. Assuming that cell A1 is your "Quantity" column label, enter the follwing formula in cell B2

=if(A1=0, 0, 1)

in cell C2:

=if(B2=0, 0, sum($b2:b2))

Copy both down.

vlookup or match should then be able to do everything for sheet B.

It also seems like a pivot table would work for this.

 

[SkipVought]

Hi,

I would use MS Query via Data/Get External Data/New Database Query -- Excel Files -- YOUR WORKBOOK -- YOUR WORKSHEET...

V UNION querie: first select to include Desc Line1 and the second for Desc Line 2.

then use Format/Conditional Formatting to make it APPEAR that the repeating values are absent -- Font Color: White.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[stillinlife101]

I might be understanding wrong, but I don't think that will work (speaking of both solutions). Worksheet B will have to fill itself by adding only lines with quantities greater than zero.

I would be okay if the method wasn't fully automatic. For instance, if another sheet copied all the items, then sorted them by quantity automatically, and I could just copy all the lines greater than zero into worksheet B. It would only be a couple seconds of work and it would be simple enough for anybody to do it.

The reason I can't do something like that in Worksheet A is that there are a number of columns in Worksheet A that don't go on the quote in Worksheet B, so I couldn't copy and paste.

To make the problem easier, I could use only one line of description per item. Any ideas?

Dan

 

[SkipVought]

I did this in about 3 minutes using my query method...
[tt]
Quantity Part# Desc#Line1 U#Price T#Price
1 pn5 desc1 9.14 9.14
1 pn5 desc2 9.14 9.14
2 pn2 desc1 5 10
2 pn2 desc2 5 10
3 pn4 desc1 1.25 3.75
3 pn4 desc2 1.25 3.75
[/tt]
the conditional format makes it APPEAR that only desc2 is visible on ever other row.

here's the query

SELECT 
  `Sheet1$`.Quantity
, `Sheet1$`.`Part#`
, `Sheet1$`.`Desc#Line1`
, `Sheet1$`.`U#Price`
, `Sheet1$`.`T#Price`

FROM `D:\My Documents\vba\db1`.`Sheet1$` `Sheet1$`
where quantity<>0

union

SELECT 
  `Sheet1$`.Quantity
, `Sheet1$`.`Part#`
, `Sheet1$`.`Desc#Line2`
, `Sheet1$`.`U#Price`
, `Sheet1$`.`T#Price`

FROM `D:\My Documents\vba\db1`.`Sheet1$` `Sheet1$`

where quantity<>0

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[mintjulep]

Or use data | filter to show only non-zeros in the quantity column, then copy visible cells only and paste to sheet B.

 

[stillinlife101]

Skip, your answer (now that I understand a little better) is obviously the way to go about this... but I'm pretty inexperienced with this and I'm having difficulty with implementation.

When I go to Data/Get External Data/New Database Query/Excel Files/File.xls, it says "This data source has no visible tables." I suspect that the spreadsheet isn't set up for this method.

I'll post a new thread since I guess that's a different topic. Thanks for your help, Skip. I think I might need your expertise again before this affair is over

Dan

 

[SkipVought]

At that point there's an OPTIONS button, that when you click, opens a window with 4 checkboxes.

May sure that the SYSTEM box is checked. As a matter of fact, ALL the boxes should be checked.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]