copy amount before TOTAL row for specific column based on matching names in column with sheet name

A

Alaa mg

Active Member
Joined
May 29, 2021
Messages
343
Office Version
  1. 2019
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.

NMJ.xlsm
ABCDE
1ITEMCUSTOMERREFDATETOTAL
21ALAARET5000024/01/20242,000.00
32ALAARET5000024/01/20243,000.00
4TOTAL5,000.00
51ALIAARET5000124/01/20243,000.00
6TOTAL3,000.00
71ALAARET5000224/01/20245,000.00
8TOTAL5,000.00
91ALIAARET5000324/01/202410,000.00
102ALIAARET5000324/01/202412,000.00
113ALIAARET5000324/01/20242,000.00
124ALIAARET5000324/01/20243,000.00
13TOTAL24/01/202427,000.00
SALL


NMJ.xlsm
ABCDE
1ITEMCUSTOMERREFDATETOTAL
21ALAAFGT56624/01/20242,200.00
32ALAAFGT56624/01/2024300.00
43ALAAFGT56624/01/2024120.00
5TOTAL2,620.00
61ALIFGT56724/01/2024220.00
7TOTAL220.00
81ALAAFGT56824/01/20245,000.00
92ALAAFGT56824/01/20243,000.00
10TOTAL8,000.00
111OMARFGT56924/01/20242,000.00
122OMARFGT57924/01/20242,000.00
13TOTAL24/01/20244,000.00
2NMRR



NMJ.xlsm
ABCDE
1ITEMCUSTOMERREFDATETOTAL
21ALAAYYYT122224/01/20246,000.00
32ALAAYYYT122224/01/20247,000.00
43ALAAYYYT122224/01/20248,000.00
54ALAAYYYT122224/01/20248,000.00
6TOTAL29,000.00
71AMIRYYYT122324/01/20242,200.00
82AMIRYYYT122324/01/20241,200.00
93AMIRYYYT122324/01/20241,100.00
10TOTAL4,500.00
111OMARYYYT122424/01/2024300.00
122OMARYYYT122424/01/2024200.00
13TOTAL24/01/2024500.00
141ALAAYYYT122524/01/2024200.00
152ALAAYYYT122524/01/20241,000.00
16TOTAL1,200.00
DFRT4


NMJ.xlsm
ABCDE
1ITEMCUSTOMERREFDATETOTAL
21ALAABMM-10024/01/20246,000.00
3TOTAL6,000.00
41ALAABMM-10124/01/2024500.00
52ALAABMM-10124/01/2024200.00
6TOTAL700.00
71OMARBMM-10224/01/2024400.00
82OMARBMM-10224/01/2024100.00
9TOTAL500.00
101OMARBMM-10324/01/2024100.00
112OMARBMM-10324/01/2024300.00
12TOTAL24/01/2024400.00
tyyt


NMJ.xlsm
ABCDEF
1DATENAMEINFORMATIONDEBITCREDITBALANCE
224/01/2024ALAAINPUT MONY5,000.005,000.00
324/01/2024OMARINPUT MONY2,000.007,000.00
424/01/2024AMIROUTPUT MONY120.006,880.00
524/01/2024ALAAOUTPUT MONY350.006,530.00
CSS
Cell Formulas
RangeFormula
F2F2=D2-E2
F3:F5F3=F2+D3-E3



CUSTOMER SHEET

NMJ.xlsm
ABCDE
1DATEINFORMATIONDEBITCREDITBALANCE
201/01/2023SALL RTR455654,000.004,000.00
301/01/2023SALL RTR455663,000.007,000.00
401/01/2023SALL RTR4556712,200.0019,200.00
501/01/2023SALL RTR4556819,200.00
601/01/20232NMRR BVG663,000.0016,200.00
701/01/20232NMRR BVG6712,000.004,200.00
801/01/2023SALL RTR455695,000.009,200.00
901/01/2023SALL RTR455707,000.0016,200.00
1001/01/2023DFRT4 NB7001,200.0015,000.00
1101/01/2023tyyt HJJ12203,000.0018,000.00
1201/01/2023INPUT MONY5,500.0012,500.00
1304/01/2023OUPUT MONY3,000.0015,500.00
14TOTAL37,200.0021,700.0037,200.00
ALAA
Cell Formulas
RangeFormula
E2E2=C2-D2
E3:E13E3=E2+C3-D3
C14:D14C14=SUM(C2:C13)
E14E14=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
ABCDE
1DATEINFORMATIONDEBITCREDITBALANCE
201/01/2023SALL RTR455654,000.004,000.00
301/01/2023SALL RTR455663,000.007,000.00
401/01/2023SALL RTR4556712,200.0019,200.00
501/01/2023SALL RTR4556819,200.00
601/01/20232NMRR BVG663,000.0016,200.00
701/01/20232NMRR BVG6712,000.004,200.00
801/01/2023SALL RTR455695,000.009,200.00
901/01/2023SALL RTR455707,000.0016,200.00
1001/01/2023DFRT4 NB7001,200.0015,000.00
1101/01/2023tyyt HJJ12203,000.0018,000.00
1201/01/2023INPUT MONY5,500.0012,500.00
1304/01/2023OUPUT MONY3,000.0015,500.00
1424/01/2024SALL RET500005,000.0020,500.00
1524/01/2024SALL RET500025,000.0025,500.00
1624/01/20242NMRR FGT5662,620.0022,880.00
1724/01/20242NMRR FGT5688,000.0014,880.00
1824/01/2024DFRT4 YYYT122229,000.00-14,120.00
1924/01/2024DFRT4 YYYT12234,500.00-18,620.00
2024/01/2024tyyt BMM-1006,000.00-12,620.00
2124/01/2024tyyt BMM-101700.00-11,920.00
2224/01/2024INPUT MONY5,000.00-16,920.00
2324/01/2024OUPUT MONY350.00-16,570.00
24TOTAL54,250.0070,820.00-16,570.00
ALAA
Cell Formulas
RangeFormula
E2,E24E2=C2-D2
E3:E23E3=E2+C3-D3
C24:D24C24=SUM(C2:C23)

thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You must log in or register to reply here.

Similar threads

A
  • Question
sorting duplicates Customers names by add two formulas in last column
Replies
3
Views
197
Kerryx
K
O
  • Question
sum or subtract for the same cell based on match column between two sheets
2
Replies
13
Views
450
Omran Y
O
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
276
skyh3ck
skyh3ck
H
  • Solved
populate dynamic list in dropdown without duplicates items based on sheet name
Replies
3
Views
160
kevin9999
K
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
248
Insurance Dave
I

Forum statistics

Threads
1,215,076
Messages
6,122,987
Members
449,093
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top