Hi,
I want to match column B in BALANCES sheet with column B in SPLIT COLUMNS sheet , then should brings amount from RESULT column( I) in SPLIT COLUMNS and put in column D for BALANCES sheet and should show calculate in column E by subtraction column C-column D and if the amount in column C bigger than column D then will show SURPLUS word in column F,if the amount in column C smaller than column D then will show DEFICIT word in column F, if the amount in column C equal column D then will show MATCHED word in column F.
result how should be
I want to match column B in BALANCES sheet with column B in SPLIT COLUMNS sheet , then should brings amount from RESULT column( I) in SPLIT COLUMNS and put in column D for BALANCES sheet and should show calculate in column E by subtraction column C-column D and if the amount in column C bigger than column D then will show SURPLUS word in column F,if the amount in column C smaller than column D then will show DEFICIT word in column F, if the amount in column C equal column D then will show MATCHED word in column F.
| Change data (2).xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | INVOICE TYP | BALANCE | RESULT | FINAL | CONDITION | ||
| 2 | 1 | PAID | 10000 | |||||
| 3 | 2 | NOT PAID | 150000 | |||||
| 4 | 3 | CASH BY SAFE | 21430 | |||||
| 5 | 4 | CASH BY BANK | 300000 | |||||
BLANACES | ||||||||
| Change data (2).xlsm | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | ITEM | INVOICE TYP | SL | ML | TL | RT | BVC | EXXP | RESULT | ||
| 2 | 1 | PAID | 63051.00 | 3424.00 | 17816.00 | 15000.00 | - | - | 62443.00 | ||
| 3 | 2 | NOT PAID | 73759.00 | 3798.00 | 4400.00 | 13200.00 | - | - | 61161.00 | ||
| 4 | 3 | CASH BY SAFE | 15000.00 | 1020.00 | 9450.00 | 2000.00 | - | - | 21430.00 | ||
| 5 | 4 | CASH BY BANK | 32452.00 | 3200.00 | 12540.00 | 13000.00 | - | - | 28792.00 | ||
SPLIT COLUMNS | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I2:I5 | I2 | =SUM(C2-D2+E2-F2+G2-H2) |
result how should be
| Change data (2).xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | INVOICE TYP | BALANCE | RESULT | FINAL | CONDITION | ||
| 2 | 1 | PAID | 10,000.00 | 62,443.00 | -52,443.00 | DEFICIT | ||
| 3 | 2 | NOT PAID | 150,000.00 | 61,161.00 | 88,839.00 | SURPLUS | ||
| 4 | 3 | CASH BY SAFE | 21,430.00 | 21,430.00 | 0.00 | MATCHED | ||
| 5 | 4 | CASH BY BANK | 300,000.00 | 28,792.00 | 271,208.00 | SURPLUS | ||
BLANACES | ||||||||
