Hello
I would match data between two sheets based on matching columns B,C,D together , then should add the values from sheet RES to sheet summary under headers BUYING and SELLING . should add the values to last column (BUYING ,SELLING), if the data in column B,C,D are new, then should match based on column A betweent sheets and insert new row for is relating part based on column A ,each part contains the item into column A in sheet summary from the first row and finish to row contains S.AG , so if any item in column A for sheet res matches with the item in frist row for sheet summary for each part ,then should add new data into this part befor row S.AG and add the values into the last columns contain headers BUYING & SELLING ,because evrey month insert three columns BUYING,SELLING,NET. the most important BUYING,SELLING should match data between two sheets based on columns B,C,D and put the values under headers .
note: about new data I highlighted for two sheets how is in sheet RES and how become in sheet SUMMARY
result in sheet summary
I would match data between two sheets based on matching columns B,C,D together , then should add the values from sheet RES to sheet summary under headers BUYING and SELLING . should add the values to last column (BUYING ,SELLING), if the data in column B,C,D are new, then should match based on column A betweent sheets and insert new row for is relating part based on column A ,each part contains the item into column A in sheet summary from the first row and finish to row contains S.AG , so if any item in column A for sheet res matches with the item in frist row for sheet summary for each part ,then should add new data into this part befor row S.AG and add the values into the last columns contain headers BUYING & SELLING ,because evrey month insert three columns BUYING,SELLING,NET. the most important BUYING,SELLING should match data between two sheets based on columns B,C,D and put the values under headers .
note: about new data I highlighted for two sheets how is in sheet RES and how become in sheet SUMMARY
SUM & ADD (1) (1) (1).xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | CLASSIFICATION | GOODS | MARK | MANFACTURE | REF | RS.N | BUYING | SELLING | NET | BUYING | SELLING | NET | BUYING | SELLING | NET | ||
2 | OIL-AS2 | 10W40 208L | CAS | SU | RE1 | RS1 | 0 | 0 | 0 | ||||||||
3 | 10W40 208L | ENI | IT | RE1 | RS1 | 0 | 0 | 0 | |||||||||
4 | 5W30 208L | Q8 | EU | RE1 | RS1 | 0 | 0 | 0 | |||||||||
5 | 10W40 208L | Q8 | EU | RE1 | RS1 | 0 | 0 | 0 | |||||||||
6 | 10W40 12x4L | Q8 | EU | RRM3 | CV3 | 0 | |||||||||||
7 | S.AG | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
8 | OIL-AS21 | 10W40 12x1L | Q8 | EU | RM1 | RC1 | 0 | 0 | |||||||||
9 | 10W40 12x1L | CAS | SU | RM1 | RC1 | 0 | 0 | ||||||||||
10 | 10W40 12x1L | ENI | IT | RM1 | RC1 | 0 | 0 | ||||||||||
11 | 15W40 12x1L | CAS | SU | RM1 | RC1 | 0 | 0 | ||||||||||
12 | S.AG | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
13 | MS-OIL1 | 5W30 12x1L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | ||||||||
14 | 10W40 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
15 | 10W40 4x4L | CAS | SU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
16 | 10W40 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
17 | 5W40 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
18 | 5W40 4x4L | CAS | SU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
19 | 5W40 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
20 | 20W50 4x4L | Q8 | EU | RRM1 | CV1 | 10 | 10 | 10 | 10 | ||||||||
21 | 20W50 4x4L | CAS | SU | RRM1 | CV1 | 12 | -12 | -12 | -12 | ||||||||
22 | 20W50 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
23 | 5W30 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
24 | S.AG | 10 | 12 | -2 | 0 | 0 | -2 | 0 | 0 | -2 | |||||||
summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O13:O23,O8:O11,L13:L23,O2:O5,L2:L5 | L2 | =I2+J2-K2 |
J7:O7,G7:H7 | G7 | =SUM(G2:G5) |
G12:O12 | G12 | =SUM(G8:G11) |
I13:I23,I2:I11 | I2 | =G2-H2 |
G24:O24 | G24 | =SUM(G13:G23) |
SUM & ADD (1) (1) (1).xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | ITEM | GOODS | MARK | MANFACTURE | REF | RS.N | BUYING | SELLING | ||
2 | OIL-AS21 | 10W40 12x1L | Q8 | EU | RM1 | RC1 | 100 | 10 | ||
3 | OIL-AS21 | 10W40 12x1L | CAS | SU | RM1 | RC1 | 600 | 5 | ||
4 | OIL-AS21 | 10W40 12x1L | ENI | IT | RM1 | RC1 | 125 | |||
5 | OIL-AS21 | 10W40 208L | CAS | SU | RE1 | RS1 | 200 | 40 | ||
6 | OIL-AS21 | 15W40 12x1L | CAS | SU | RM1 | RC1 | 131 | |||
7 | OIL-AS2 | 10W40 208L | ENI | IT | RE1 | RS1 | 400 | 20 | ||
8 | OIL-AS2 | 5W30 208L | Q8 | EU | RE1 | RS1 | 60 | 20 | ||
9 | MS-OIL1 | 20W50 4x4L | Q8 | EU | RRM1 | CV1 | 10 | |||
10 | MS-OIL1 | 20W50 4x4L | CAS | SU | RRM1 | CV1 | 12 | |||
11 | MS-OIL1 | 20W50 4x6L | CAS | SU | RRM2 | CV2 | 20 | 13 | ||
12 | OIL-AS2 | 10W40 12x4L | Q8 | EU | RRM3 | CV3 | 20 | 14 | ||
RES |
result in sheet summary
SUM & ADD (1) (1) (1).xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | CLASSIFICATION | GOODS | MARK | MANFACTURE | REF | RS.N | BUYING | SELLING | NET | BUYING | SELLING | NET | BUYING | SELLING | NET | ||
2 | OIL-AS2 | 10W40 208L | CAS | SU | RE1 | RS1 | 0 | 0 | 0 | ||||||||
3 | 10W40 208L | ENI | IT | RE1 | RS1 | 400 | 20 | 380 | 380 | 380 | |||||||
4 | 5W30 208L | Q8 | EU | RE1 | RS1 | 60 | 20 | 40 | 40 | 40 | |||||||
5 | 10W40 208L | Q8 | EU | RE1 | RS1 | 0 | 0 | 0 | |||||||||
6 | 10W40 12x4L | Q8 | EU | RRM3 | CV3 | 20 | 14 | 6 | 6 | 6 | |||||||
7 | S.AG | 460 | 40 | 420 | 0 | 0 | 420 | 0 | 0 | 420 | |||||||
8 | OIL-AS2 | 10W40 12x1L | Q8 | EU | RM1 | RC1 | 100 | 10 | 90 | 0 | |||||||
9 | 10W40 12x1L | CAS | SU | RM1 | RC1 | 600 | 5 | 595 | 0 | ||||||||
10 | 10W40 12x1L | ENI | IT | RM1 | RC1 | 125 | 125 | 0 | |||||||||
11 | 15W40 12x1L | CAS | SU | RM1 | RC1 | 131 | 131 | 0 | |||||||||
12 | S.AG | 956 | 15 | 941 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
13 | MS-OIL1 | 5W30 12x1L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | ||||||||
14 | 10W40 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
15 | 10W40 4x4L | CAS | SU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
16 | 10W40 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
17 | 5W40 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
18 | 5W40 4x4L | CAS | SU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
19 | 5W40 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
20 | 20W50 4x4L | Q8 | EU | RRM1 | CV1 | 10 | 10 | 10 | 10 | ||||||||
21 | 20W50 4x4L | CAS | SU | RRM1 | CV1 | 12 | -12 | -12 | -12 | ||||||||
22 | 20W50 4x4L | ENI | IT | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
23 | 5W30 4x4L | Q8 | EU | RRM1 | CV1 | 0 | 0 | 0 | |||||||||
24 | 20W50 4x6L | CAS | SU | RRM2 | CV2 | 20 | 13 | 7 | 7 | 7 | |||||||
25 | S.AG | 10 | 12 | -2 | 0 | 0 | -2 | 0 | 0 | -2 | |||||||
summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O13:O24,O8:O11,O2:O6,L13:L24,L2:L6 | L2 | =I2+J2-K2 |
J7:O7,G7:H7 | G7 | =SUM(G2:G5) |
G12:O12 | G12 | =SUM(G8:G11) |
I13:I24,I2:I11 | I2 | =G2-H2 |
G25:O25 | G25 | =SUM(G13:G23) |