Add Lines with Total Quantity If Found

[tizdall]

Hello!

I have lists of product information on a worksheet and I need to add Screws to an order if one of our products exists in the list on the worksheet.

First, I need to find the part I am looking for in the column, sum the quantities and then add 2 rows to the last row with data displaying the total quantity*1.2.

As an example, I would start with this in excel:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Product Name[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Plank[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Board[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]HW[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Board[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Nails[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


And I want it to add 2 lines to the last row (won't always be row 14, I'll need to find the last row with data) so it would look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Product Name[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Plank[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Board[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]HW[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Board[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Nails[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Screws[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Screws[/TD]
[TD]5*1.2[/TD]
[/TR]
</tbody>[/TABLE]

In this example I need to add 1.2 screws for every board in the list. So the total number of boards would be 5 so the total number of screws i need would be 6.

If the file does not contain the product then no screws would be added.

I am hoping I can make this using vba so that I can hit a button, it will see if there are 'boards.' If there are, sum the total quantity of 'boards' in the list, multiply it by 1.2 and display that amount in two new rows added to the bottom of the last row with data. The name should read 'Screws' for these lines.

Any help would be greatly appreciated!!

 

[venkat1926]

Sheet2

*	A	B	C
Row	Product Name	Quantity
Plank
Board
HW
Board
Nails
Screws	*
Screws

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]6[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C8 =SUMPRODUCT((B2:B6="board")*(C2:C6))*1.2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
see formla in c8 (why two screws??????)