Hello
I have data for each customer in sheets SALL,2NMRR,DFRT4,tyyt,CSS . the customer is existed in column B for theses sheets should match with sheet name for CUSTOMER sheet where contains amounts for debit ,credit, balance . should brings the amount from last row contains TOTAL row in sheets SALL,2NMRR,DFRT4,tyyt as to CSS sheet there is no lastrow (TOTAL) just brings the amount based on the column B for each customer.
CUSTOMER SHEET
when match customer sheet name with others sheets based on column B then will add to columns(debit,credit) before total row for customer sheet
so the amounts for sheets SALL,tyyt should copy to column C for customer sheet and populate date in column A populate ref with merge for sheet name when put in cell for column B as I did in CUSTOMER sheet .
as to the amounts for sheets 2NMRR,DFRT4 should copy to column D for customer sheet and populate date today in column A and populate ref with merge for sheet name when put in cell for column B as I did in CUSTOMER sheet from column C,D for sheets 2NMRR,DFRT4 without forget CCS sheet should brings amount based on two words and invert the column if the word is INPUT MONEY then will put in column D for the customer sheet with copy the word and date from CCS to customer sheet , if the word is OUTPUT MONEY then will put in column C for the customer sheet with copy the word and date from CCS to customer sheet .but when brings the amounts across sheets should match column DATE with DATE(TODAY) if it's not matched with date today then just bring data for matching with date today.
there are 6000 rows for each sheet , about 15 customers sheets for matching .
so the result in CUSTOMER sheet will be(Highlighted rows)
thanks
I have data for each customer in sheets SALL,2NMRR,DFRT4,tyyt,CSS . the customer is existed in column B for theses sheets should match with sheet name for CUSTOMER sheet where contains amounts for debit ,credit, balance . should brings the amount from last row contains TOTAL row in sheets SALL,2NMRR,DFRT4,tyyt as to CSS sheet there is no lastrow (TOTAL) just brings the amount based on the column B for each customer.
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | CUSTOMER | REF | DATE | TOTAL | ||
2 | 1 | ALAA | RET50000 | 24/01/2024 | 2,000.00 | ||
3 | 2 | ALAA | RET50000 | 24/01/2024 | 3,000.00 | ||
4 | TOTAL | 5,000.00 | |||||
5 | 1 | ALIAA | RET50001 | 24/01/2024 | 3,000.00 | ||
6 | TOTAL | 3,000.00 | |||||
7 | 1 | ALAA | RET50002 | 24/01/2024 | 5,000.00 | ||
8 | TOTAL | 5,000.00 | |||||
9 | 1 | ALIAA | RET50003 | 24/01/2024 | 10,000.00 | ||
10 | 2 | ALIAA | RET50003 | 24/01/2024 | 12,000.00 | ||
11 | 3 | ALIAA | RET50003 | 24/01/2024 | 2,000.00 | ||
12 | 4 | ALIAA | RET50003 | 24/01/2024 | 3,000.00 | ||
13 | TOTAL | 24/01/2024 | 27,000.00 | ||||
SALL |
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | CUSTOMER | REF | DATE | TOTAL | ||
2 | 1 | ALAA | FGT566 | 24/01/2024 | 2,200.00 | ||
3 | 2 | ALAA | FGT566 | 24/01/2024 | 300.00 | ||
4 | 3 | ALAA | FGT566 | 24/01/2024 | 120.00 | ||
5 | TOTAL | 2,620.00 | |||||
6 | 1 | ALI | FGT567 | 24/01/2024 | 220.00 | ||
7 | TOTAL | 220.00 | |||||
8 | 1 | ALAA | FGT568 | 24/01/2024 | 5,000.00 | ||
9 | 2 | ALAA | FGT568 | 24/01/2024 | 3,000.00 | ||
10 | TOTAL | 8,000.00 | |||||
11 | 1 | OMAR | FGT569 | 24/01/2024 | 2,000.00 | ||
12 | 2 | OMAR | FGT579 | 24/01/2024 | 2,000.00 | ||
13 | TOTAL | 24/01/2024 | 4,000.00 | ||||
2NMRR |
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | CUSTOMER | REF | DATE | TOTAL | ||
2 | 1 | ALAA | YYYT1222 | 24/01/2024 | 6,000.00 | ||
3 | 2 | ALAA | YYYT1222 | 24/01/2024 | 7,000.00 | ||
4 | 3 | ALAA | YYYT1222 | 24/01/2024 | 8,000.00 | ||
5 | 4 | ALAA | YYYT1222 | 24/01/2024 | 8,000.00 | ||
6 | TOTAL | 29,000.00 | |||||
7 | 1 | AMIR | YYYT1223 | 24/01/2024 | 2,200.00 | ||
8 | 2 | AMIR | YYYT1223 | 24/01/2024 | 1,200.00 | ||
9 | 3 | AMIR | YYYT1223 | 24/01/2024 | 1,100.00 | ||
10 | TOTAL | 4,500.00 | |||||
11 | 1 | OMAR | YYYT1224 | 24/01/2024 | 300.00 | ||
12 | 2 | OMAR | YYYT1224 | 24/01/2024 | 200.00 | ||
13 | TOTAL | 24/01/2024 | 500.00 | ||||
14 | 1 | ALAA | YYYT1225 | 24/01/2024 | 200.00 | ||
15 | 2 | ALAA | YYYT1225 | 24/01/2024 | 1,000.00 | ||
16 | TOTAL | 1,200.00 | |||||
DFRT4 |
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | CUSTOMER | REF | DATE | TOTAL | ||
2 | 1 | ALAA | BMM-100 | 24/01/2024 | 6,000.00 | ||
3 | TOTAL | 6,000.00 | |||||
4 | 1 | ALAA | BMM-101 | 24/01/2024 | 500.00 | ||
5 | 2 | ALAA | BMM-101 | 24/01/2024 | 200.00 | ||
6 | TOTAL | 700.00 | |||||
7 | 1 | OMAR | BMM-102 | 24/01/2024 | 400.00 | ||
8 | 2 | OMAR | BMM-102 | 24/01/2024 | 100.00 | ||
9 | TOTAL | 500.00 | |||||
10 | 1 | OMAR | BMM-103 | 24/01/2024 | 100.00 | ||
11 | 2 | OMAR | BMM-103 | 24/01/2024 | 300.00 | ||
12 | TOTAL | 24/01/2024 | 400.00 | ||||
tyyt |
NMJ.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | NAME | INFORMATION | DEBIT | CREDIT | BALANCE | ||
2 | 24/01/2024 | ALAA | INPUT MONY | 5,000.00 | 5,000.00 | |||
3 | 24/01/2024 | OMAR | INPUT MONY | 2,000.00 | 7,000.00 | |||
4 | 24/01/2024 | AMIR | OUTPUT MONY | 120.00 | 6,880.00 | |||
5 | 24/01/2024 | ALAA | OUTPUT MONY | 350.00 | 6,530.00 | |||
CSS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3:F5 | F3 | =F2+D3-E3 |
CUSTOMER SHEET
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | INFORMATION | DEBIT | CREDIT | BALANCE | ||
2 | 01/01/2023 | SALL RTR45565 | 4,000.00 | 4,000.00 | |||
3 | 01/01/2023 | SALL RTR45566 | 3,000.00 | 7,000.00 | |||
4 | 01/01/2023 | SALL RTR45567 | 12,200.00 | 19,200.00 | |||
5 | 01/01/2023 | SALL RTR45568 | 19,200.00 | ||||
6 | 01/01/2023 | 2NMRR BVG66 | 3,000.00 | 16,200.00 | |||
7 | 01/01/2023 | 2NMRR BVG67 | 12,000.00 | 4,200.00 | |||
8 | 01/01/2023 | SALL RTR45569 | 5,000.00 | 9,200.00 | |||
9 | 01/01/2023 | SALL RTR45570 | 7,000.00 | 16,200.00 | |||
10 | 01/01/2023 | DFRT4 NB700 | 1,200.00 | 15,000.00 | |||
11 | 01/01/2023 | tyyt HJJ1220 | 3,000.00 | 18,000.00 | |||
12 | 01/01/2023 | INPUT MONY | 5,500.00 | 12,500.00 | |||
13 | 04/01/2023 | OUPUT MONY | 3,000.00 | 15,500.00 | |||
14 | TOTAL | 37,200.00 | 21,700.00 | 37,200.00 | |||
ALAA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | E2 | =C2-D2 |
E3:E13 | E3 | =E2+C3-D3 |
C14:D14 | C14 | =SUM(C2:C13) |
E14 | E14 | =C14-F14 |
when match customer sheet name with others sheets based on column B then will add to columns(debit,credit) before total row for customer sheet
so the amounts for sheets SALL,tyyt should copy to column C for customer sheet and populate date in column A populate ref with merge for sheet name when put in cell for column B as I did in CUSTOMER sheet .
as to the amounts for sheets 2NMRR,DFRT4 should copy to column D for customer sheet and populate date today in column A and populate ref with merge for sheet name when put in cell for column B as I did in CUSTOMER sheet from column C,D for sheets 2NMRR,DFRT4 without forget CCS sheet should brings amount based on two words and invert the column if the word is INPUT MONEY then will put in column D for the customer sheet with copy the word and date from CCS to customer sheet , if the word is OUTPUT MONEY then will put in column C for the customer sheet with copy the word and date from CCS to customer sheet .but when brings the amounts across sheets should match column DATE with DATE(TODAY) if it's not matched with date today then just bring data for matching with date today.
there are 6000 rows for each sheet , about 15 customers sheets for matching .
so the result in CUSTOMER sheet will be(Highlighted rows)
NMJ.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | INFORMATION | DEBIT | CREDIT | BALANCE | ||
2 | 01/01/2023 | SALL RTR45565 | 4,000.00 | 4,000.00 | |||
3 | 01/01/2023 | SALL RTR45566 | 3,000.00 | 7,000.00 | |||
4 | 01/01/2023 | SALL RTR45567 | 12,200.00 | 19,200.00 | |||
5 | 01/01/2023 | SALL RTR45568 | 19,200.00 | ||||
6 | 01/01/2023 | 2NMRR BVG66 | 3,000.00 | 16,200.00 | |||
7 | 01/01/2023 | 2NMRR BVG67 | 12,000.00 | 4,200.00 | |||
8 | 01/01/2023 | SALL RTR45569 | 5,000.00 | 9,200.00 | |||
9 | 01/01/2023 | SALL RTR45570 | 7,000.00 | 16,200.00 | |||
10 | 01/01/2023 | DFRT4 NB700 | 1,200.00 | 15,000.00 | |||
11 | 01/01/2023 | tyyt HJJ1220 | 3,000.00 | 18,000.00 | |||
12 | 01/01/2023 | INPUT MONY | 5,500.00 | 12,500.00 | |||
13 | 04/01/2023 | OUPUT MONY | 3,000.00 | 15,500.00 | |||
14 | 24/01/2024 | SALL RET50000 | 5,000.00 | 20,500.00 | |||
15 | 24/01/2024 | SALL RET50002 | 5,000.00 | 25,500.00 | |||
16 | 24/01/2024 | 2NMRR FGT566 | 2,620.00 | 22,880.00 | |||
17 | 24/01/2024 | 2NMRR FGT568 | 8,000.00 | 14,880.00 | |||
18 | 24/01/2024 | DFRT4 YYYT1222 | 29,000.00 | -14,120.00 | |||
19 | 24/01/2024 | DFRT4 YYYT1223 | 4,500.00 | -18,620.00 | |||
20 | 24/01/2024 | tyyt BMM-100 | 6,000.00 | -12,620.00 | |||
21 | 24/01/2024 | tyyt BMM-101 | 700.00 | -11,920.00 | |||
22 | 24/01/2024 | INPUT MONY | 5,000.00 | -16,920.00 | |||
23 | 24/01/2024 | OUPUT MONY | 350.00 | -16,570.00 | |||
24 | TOTAL | 54,250.00 | 70,820.00 | -16,570.00 | |||
ALAA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2,E24 | E2 | =C2-D2 |
E3:E23 | E3 | =E2+C3-D3 |
C24:D24 | C24 | =SUM(C2:C23) |
thanks