# combine two sheets with condition

Discussion in 'Business Applications' started by deep1780, Dec 6, 2011.

Not open for further replies.

Joined:
Dec 6, 2011
Messages:
2
i want a vba code for combining sheets
with condition
sheet1 :
column a = 101 , 102 , 105, 108 , 115
column b = 5, 2 , 3 , 4 , 5
sheet2 :
column a = 101 , 102 , 103 , 104 , 105 , 106 , 107 , 108 , 109 ..........
column b = 10 , 20 , 30 , 40 , 20 , 35 , 30 , 15 , 0 .........
ans could be :
column b = (10+5)15 , (20+2)22 , 30 , 40 , (20+3)23 , 35 , 30 , 15 , (0+5)5 .........

2. ### scotty718

Joined:
Nov 19, 2010
Messages:
185
Welcome to the board, deep1780.

Unfortunately, your description is not very clear. Can you provide a spreadsheet example with a before and after scenario. So, maybe two sheets that you start with, and third sheet that has the result you wish to end up with?

I, or someone else, will be able to help you much better this way.

3. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
You could add a third column on either one of the sheets to add those numbers together if you'd like. Assuming you are using Sheet2 as your final sheet (assuming you want to delete Sheet1), in C1 add the formula:

Code:
`=sumif(Sheet1!A:A,A1,Sheet1!B:B)+B1`
This assumes you have no headers. If you have headers, start your formula in C2 and amend the two cell references in the formula accordingly (i.e. A1 becomes A2, B1 becomes B2).

Copy the formula down. Select the cell with the formula in it (assuming you've already entered it and confirmed it), hover over the bottom right corner until your cursor turns to a bold plus sign ( + ) and double click. Select column C, press Ctrl + C, then press Alt + E, then S, then V, then Enter. (This pastes special as values.)

Let us know if this helps.

HTH

Joined:
Dec 6, 2011
Messages:
2
in this workbook i did
on invoice sheet
when i enter product code in form "frmmakinginvoice" via clicking "add product"
nd then quantity of product in textbox2,,,
then with pressing
quantity on dpw sheet (column "stock") will be less
but actualy i want stock less after making invoice with pressing "save" on "frmmakinginvoice"

File size:
126.7 KB
Views:
35
5. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Uh, ok. So this is a bit more than you explained. So you want to run what goes when you click the "OK" button when you click the "SAVE" button? If so, in your save button just call that routine...
Code:
` Call CommandButton1_Click`
Other than that, I'm not entirely sure what you mean. If you would like something else, please define what it is you want a little more. The more details you give, the better shot you'll get at having a solution.

As Seen On