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
378
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You must log in or register to reply here.

Similar threads

A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
199
Alaa mg
A
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
187
Omran Y
O
A
  • Solved
Extract duplicates dates and insert TOTAL row for each duplicates dates
Replies
4
Views
131
Alaa mg
A
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
207
Omran Y
O
O
  • Solved
show data on userform vertically based on combobox for multiple ranges
Replies
2
Views
146
Omran Y
O

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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