hi experts
I have two sheets DATA and RESULT . the result should be in sheet RESULT . so what I want match data between two sheets in COL B,C ,D for each item in COL A the values should be in last columns IMPORT, EXPORT because every time I insert theses columns
as you see each item in COL A contains data based on starts from first cell in COL A and end to last empty cell before start new item for both sheets to make easy match data .
when if new data are existed in sheet data but not existed in sheet before then add to before TOTAL row as highlighted by red and if there are existed into two sheets then should pull the values and put in last columns IMPORT , EXPORT as highlighted by blue after match the data for each item in COLA . by the way my real data are about 3000 rows and will increase continuously in sheet DATA . may be asking yourself about the values in col E,F,G . it depend on sheet data . every time change the values and add a new data then should show the values next to empty columns IMPORT, EXPORT
note: when insert new row should not affect for the borders and formulas . I put the result in sheet result
sheet data
sheet result before
sheet result after
I have two sheets DATA and RESULT . the result should be in sheet RESULT . so what I want match data between two sheets in COL B,C ,D for each item in COL A the values should be in last columns IMPORT, EXPORT because every time I insert theses columns
as you see each item in COL A contains data based on starts from first cell in COL A and end to last empty cell before start new item for both sheets to make easy match data .
when if new data are existed in sheet data but not existed in sheet before then add to before TOTAL row as highlighted by red and if there are existed into two sheets then should pull the values and put in last columns IMPORT , EXPORT as highlighted by blue after match the data for each item in COLA . by the way my real data are about 3000 rows and will increase continuously in sheet DATA . may be asking yourself about the values in col E,F,G . it depend on sheet data . every time change the values and add a new data then should show the values next to empty columns IMPORT, EXPORT
note: when insert new row should not affect for the borders and formulas . I put the result in sheet result
sheet data
| ttt.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | DEL NO | BATCH NO | TTL | TT1 | IMPORT | EXPORT | ||
| 2 | CC-1 | CC-1 | SS-1 | TRU | 12 | 10 | ||
| 3 | CC-1 | SS-1 | LTR | 12 | 5 | |||
| 4 | CC-2 | SS-2 | FG | 5 | 5 | |||
| 5 | CC-2 | CC-1 | SS-1 | TRR | 10 | 5 | ||
| 6 | CC-1 | SS-1 | LTR | 20 | 10 | |||
| 7 | CD-1 | CS-1 | LL-1 | RRL | 15 | 5 | ||
| 8 | CS-2 | LL-2 | TTY | 10 | 10 | |||
| 9 | CS-3 | LL-3 | MMW | 20 | 10 | |||
| 10 | CS-4 | LL-4 | NNW | 10 | 10 | |||
| 11 | CCL | CC-2 | SS-2 | LTR | 21 | 5 | ||
| 12 | CCM | CC-3 | SS-3 | LTR | 22 | 5 | ||
DATA | ||||||||
sheet result before
| ttt.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | DEL NO | BATCH NO | TTL | TT1 | IMPORT | EXPORT | BALANCE | IMPORT | EXPORT | BALANCE | IMPORT | EXPORT | BALANCE | ||
| 2 | CC-1 | CC-1 | SS-1 | TRU | 120 | 10 | 110 | 0 | 0 | ||||||
| 3 | CC-1 | SS-1 | LTR | 10 | 5 | 5 | 0 | 0 | |||||||
| 4 | TOTAL | 130 | 15 | 115 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| 5 | CC-2 | CC-1 | SS-1 | TRR | 15 | 5 | 10 | 0 | 0 | ||||||
| 6 | CC-1 | SS-1 | LTR | 10 | 10 | 0 | 0 | ||||||||
| 7 | TOTAL | 25 | 5 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| 8 | CD-1 | CS-1 | LL-1 | RRL | 10 | 10 | 0 | 0 | |||||||
| 9 | CS-2 | LL-2 | TTY | 5 | 5 | 0 | 0 | ||||||||
| 10 | CS-3 | LL-3 | MMW | 5 | 5 | 0 | 0 | ||||||||
| 11 | TOTAL | 20 | 0 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| 12 | CCL | CC-2 | SS-2 | LTR | 10 | 10 | 0 | 0 | |||||||
| 13 | TOTAL | 10 | 0 | 35 | 0 | 0 | 0 | 0 | 0 | 0 | |||||
| 14 | CCM | CC-3 | SS-3 | LTR | 10 | 22 | 22 | 23 | |||||||
| 15 | TOTAL | 10 | 0 | 22 | 0 | 0 | 22 | 0 | 0 | 23 | |||||
RESULT | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K15:L15,H15:I15,K13:L13,H13:I13,E7:M7,E4:M4 | E4 | =SUM(E2:E3) |
| F13:G13,M11,J11,E11:G11 | E11 | =SUM(E8:E10) |
| M15,J15,E15:G15,M13,J13,E13,K11:L11,H11:I11 | H11 | =SUM(H10:H10) |
| M12,M8:M10,M5:M6,M2:M3,J12,J8:J10,J5:J6,J2:J3,G12,G8:G10,G5:G6,G2:G3 | G2 | =E2-F2 |
sheet result after
| ttt.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DEL NO | BATCH NO | TTL | TT1 | IMPORT | EXPORT | BALANCE | IMPORT | EXPORT | BALANCE | ||
| 2 | CC-1 | CC-1 | SS-1 | TRU | 120 | 10 | 110 | 12 | 10 | 2 | ||
| 3 | CC-1 | SS-1 | LTR | 10 | 5 | 5 | 12 | 5 | 7 | |||
| 4 | CC-2 | SS-2 | FG | 0 | 5 | 5 | ||||||
| 5 | TOTAL | 130 | 15 | 115 | 29 | 15 | 14 | |||||
| 6 | CC-2 | CC-1 | SS-1 | TRR | 15 | 5 | 10 | 10 | 5 | 5 | ||
| 7 | CC-1 | SS-1 | LTR | 10 | 10 | 20 | 10 | 10 | ||||
| 8 | TOTAL | 25 | 5 | 20 | 15 | |||||||
| 9 | CD-1 | CS-1 | LL-1 | RRL | 10 | 10 | 15 | 5 | 10 | |||
| 10 | CS-2 | LL-2 | TTY | 5 | 5 | 10 | 10 | 0 | ||||
| 11 | CS-3 | LL-3 | MMW | 5 | 5 | 20 | 10 | 10 | ||||
| 12 | CS-4 | LL-4 | NNW | 0 | 10 | 10 | 0 | |||||
| 13 | TOTAL | 20 | 0 | 20 | 55 | 35 | 20 | |||||
| 14 | CCL | CC-2 | SS-2 | LTR | 10 | 10 | 21 | 5 | 16 | |||
| 15 | TOTAL | 10 | 0 | 10 | 16 | |||||||
| 16 | CCM | CC-3 | SS-3 | LTR | 10 | 22 | 22 | 5 | 22 | |||
| 17 | TOTAL | 10 | 0 | 22 | 22 | |||||||
AFTER | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5:J5 | E5 | =SUM(E2:E4) |
| J8,E8:G8 | E8 | =SUM(E6:E7) |
| E13:J13 | E13 | =SUM(E9:E12) |
| J14,J9:J12,J6:J7,J2:J4,G14,G9:G12,G6:G7,G2:G4 | G2 | =E2-F2 |
| J17,E17:G17,J15,E15:G15 | E15 | =SUM(E14:E14) |
