Hi
I hope finding macro for bout 11000 rows between two sheets
should delete the items and add the others in sheet main based on matching sheet DATA with this cases
1- should match the partial text in column C (INVOICE,RECEIVABLE) and match the values whether in column D or E .
2- if there is the same partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for two sheets , then shouldn't delete from sheet main with considering the repeated items should be the same in counting
this means if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for are repeated four times in sheet DATA but in sheet main are repeated three times then should add the fourth duplicates item into sheet MAIN to become four items as sheet DATA . as to if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for are repeated four times in sheet MAIN but in sheet DATA are repeated three times then should delete the the fourth item duplicated from sheet MAIN to become equal three duplicates items like sheet DATA .
3- if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E in sheet DATA but not repeated item and not existed in sheet MAIN then should add it to sheet MAIN ,if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E in sheet MAIN but not repeated item and not existed in sheet DATA then should delete it from sheet MAIN
4- any thing else except (INVOICE,RECEIVABLE) is existed in sheet MAIN then should deleted except row2 contains OPENING in column C.every time I will add new data in two sheets.
5- should highlight new items are existed in sheet but not is another as I did it .
the matching should be based on partial text and the same values together .
last thing I want the result in sheet OUTPUT with keeping the original data in two sheets ,should clear data in sheet output before bring data when run the macro . and sort it the first invoice and the second RECEIVABLE with make the duplicates values under each other of them .
result
sorry for big explanation , but it's necessary
I hope finding macro for bout 11000 rows between two sheets
should delete the items and add the others in sheet main based on matching sheet DATA with this cases
1- should match the partial text in column C (INVOICE,RECEIVABLE) and match the values whether in column D or E .
2- if there is the same partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for two sheets , then shouldn't delete from sheet main with considering the repeated items should be the same in counting
this means if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for are repeated four times in sheet DATA but in sheet main are repeated three times then should add the fourth duplicates item into sheet MAIN to become four items as sheet DATA . as to if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E for are repeated four times in sheet MAIN but in sheet DATA are repeated three times then should delete the the fourth item duplicated from sheet MAIN to become equal three duplicates items like sheet DATA .
3- if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E in sheet DATA but not repeated item and not existed in sheet MAIN then should add it to sheet MAIN ,if I have partial text in column C (INVOICE,RECEIVABLE) and the same values in column D or E in sheet MAIN but not repeated item and not existed in sheet DATA then should delete it from sheet MAIN
4- any thing else except (INVOICE,RECEIVABLE) is existed in sheet MAIN then should deleted except row2 contains OPENING in column C.every time I will add new data in two sheets.
5- should highlight new items are existed in sheet but not is another as I did it .
the matching should be based on partial text and the same values together .
last thing I want the result in sheet OUTPUT with keeping the original data in two sheets ,should clear data in sheet output before bring data when run the macro . and sort it the first invoice and the second RECEIVABLE with make the duplicates values under each other of them .
| cs1 ans.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | DATE | DESCRIPE | DEBIT | CREDIT | BALANCE | ||
| 2 | 1 | 1/1/2021 | OPENINIG | 1000000 | 1000000 | |||
| 3 | 2 | 1/2/2021 | INVOICE-A234 | 10000 | 1,010,000.00 | |||
| 4 | 3 | 1/3/2021 | RECEIVABLE VOUCHER A233 | 1,500.00 | 1,008,500.00 | |||
| 5 | 4 | 1/4/2021 | RECEIVABLE VOUCHER A234 | 500.00 | 1,008,000.00 | |||
| 6 | 5 | 1/5/2021 | INVOICE-A233 | 2000 | 1,010,000.00 | |||
| 7 | 5 | 1/6/2021 | INVOICE-A234 | 2000 | 1,012,000.00 | |||
| 8 | 6 | 1/7/2021 | INVOICE-A235 | 3000 | 1,015,000.00 | |||
| 9 | 7 | 1/8/2021 | INVOICE-A236 | 200 | 1,015,200.00 | |||
| 10 | 8 | 1/9/2021 | INVOICE-A237 | 300 | 1,015,500.00 | |||
| 11 | 9 | 1/10/2021 | INVOICE-A238 | 400 | 1,015,900.00 | |||
| 12 | 10 | 1/5/2021 | RECIEVABLE VOUCHER A235 | 500.00 | 1,015,400.00 | |||
| 13 | 11 | 1/14/2021 | RECIEVABLE VOUCHER A236 | 500.00 | 1,014,900.00 | |||
| 14 | 12 | 1/13/2021 | RECEIVABLE VOUCHER A237 | 1,000.00 | 1,013,900.00 | |||
| 15 | 13 | 1/15/2021 | RECEIVABLE VOUCHER A238 | 500.00 | 1,013,400.00 | |||
| 16 | 14 | 1/15/2021 | INVOICE-A239 | 2000 | 1,015,400.00 | |||
data | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2 | F2 | =D2-E2 |
| F3:F16 | F3 | =F2+D3-E3 |
| cs1 ans.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | DATE | DESCRIPE | DEBIT | CREDIT | BALANCE | ||
| 2 | 1 | 1/1/2021 | OPENINIG | 1000000 | 1,000,000.00 | |||
| 3 | 2 | 1/2/2021 | INVOICE-MS22 | 10000 | 1,010,000.00 | |||
| 4 | 3 | 1/3/2021 | RECEIVABLE VOUCHER M44 | 1,500.00 | 1,008,500.00 | |||
| 5 | 4 | 1/4/2021 | RECEIVABLE VOUCHER NH23 | 500.00 | 1,008,000.00 | |||
| 6 | 5 | 1/5/2021 | RECEIVABLE VOUCHER NH24 | 500.00 | 1,007,500.00 | |||
| 7 | 6 | 1/6/2021 | INVOICE NO STR234 | 2000 | 1,009,500.00 | |||
| 8 | 7 | 1/7/2021 | INVOICE NO STR235 | 3000 | 1,012,500.00 | |||
| 9 | 8 | 1/8/2021 | INVOICE NO STR236 | 200 | 1,012,700.00 | |||
| 10 | 9 | 1/9/2021 | INVOICE NO STR237 | 300 | 1,013,000.00 | |||
| 11 | 10 | 1/10/2021 | INVOICE NO STR238 | 400 | 1,013,400.00 | |||
| 12 | 11 | 1/11/2021 | INVOICE NO STR239 | 1500 | 1,014,900.00 | |||
| 13 | 12 | 1/12/2021 | PAYAPALE VOUCHER A235 | 1000 | 1,015,900.00 | |||
| 14 | 13 | 1/13/2021 | PAYAPALE VOUCHER A236 | 500.00 | 1,015,400.00 | |||
| 15 | 14 | 1/14/2021 | RECEIVABLE VOUCHER NH25 | 500.00 | 1,014,900.00 | |||
| 16 | 15 | 1/15/2021 | RECEIVABLEVOUCHER NH26 | 1,500.00 | 1,013,400.00 | |||
| 17 | 16 | 1/16/2021 | INVOICE NO STR240 | 400 | 1,013,800.00 | |||
| 18 | 17 | 1/17/2021 | INVOICE NO STR241 | 400 | 1,014,200.00 | |||
main | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2 | F2 | =D2-E2 |
| F3:F18 | F3 | =F2+D3-E3 |
result
| cs1 ans.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | DATE | DESCRIPE | DEBIT | CREDIT | BALANCE | ||
| 2 | 1 | 1/1/2021 | OPENINIG | 1000000 | 1,000,000.00 | |||
| 3 | 2 | 1/2/2021 | INVOICE-MS22 | 10000 | 1,010,000.00 | |||
| 4 | 3 | 1/6/2021 | INVOICE NO STR234 | 2000 | 1,012,000.00 | |||
| 5 | 4 | 1/6/2021 | INVOICE-A234 | 2000 | 1,014,000.00 | |||
| 6 | 5 | 1/15/2021 | INVOICE-A239 | 2000 | 1,016,000.00 | |||
| 7 | 6 | 1/7/2021 | INVOICE NO STR235 | 3000 | 1,019,000.00 | |||
| 8 | 7 | 1/8/2021 | INVOICE NO STR236 | 200 | 1,019,200.00 | |||
| 9 | 8 | 1/9/2021 | INVOICE NO STR237 | 300 | 1,019,500.00 | |||
| 10 | 9 | 1/10/2021 | INVOICE NO STR238 | 400 | 1,019,900.00 | |||
| 11 | 10 | 1/13/2021 | RECIEVABLE VOUCHER A237 | 1,000.00 | 1,018,900.00 | |||
| 12 | 11 | 1/3/2021 | RECIEVABLE VOUCHER M44 | 1,500.00 | 1,017,400.00 | |||
| 13 | 12 | 1/4/2021 | RECIEVABLE VOUCHER NH23 | 500.00 | 1,016,900.00 | |||
| 14 | 13 | 1/5/2021 | RECIEVABLE VOUCHER NH24 | 500.00 | 1,016,400.00 | |||
| 15 | 14 | 1/14/2021 | RECIEVABLE VOUCHER NH25 | 500.00 | 1,015,900.00 | |||
| 16 | 15 | 1/14/2021 | RECIEVABLE VOUCHER A238 | 500.00 | 1,015,400.00 | |||
output | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2 | F2 | =D2-E2 |
| F3:F16 | F3 | =F2+D3-E3 |
sorry for big explanation , but it's necessary
