compare data across sheets for 6000 rows for each sheet individual based on sheet name

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
354
Office Version
  1. 2016
Platform
  1. Windows
Hi
I I no know if macro could deal with 6000 rows for each individual sheet when match sheet names "customers with two sheets " BUYING,SALES" for column name(C).
so in REPORT sheet should populate as I did it.
I want populating the customer name and the invoice number in columns B,C bases on sheets BUYING ,SALES also should populate amounts under BUYING AMOUNT,SALES AMOUNT IN columns D,E based what are existed in BUYING ,SALES sheets in TOTAL row for column J .as to populate amounts in columns F,G from customers sheet CR-1000,CR-1001 then should match sheet name for customers with column NAME (C) for BUYING ,SALES sheets and match invoice number based on last part in column B for customers sheets with INVOICE NO column for BUYING ,SALES sheets and brings values from CREDIT & DEBIT columns in customers sheets .
if there is customers contains amounts are not existed in customers sheets then should also add it in report sheet to compare it after that should calculate in columns H,I as I did .
every time will add new customers sheets and add new data in BUYING ,SALES sheets so should clear cells in report sheet
before bring data when run the macro every time.

KLL.xlsm
ABCDEFGHIJ
1ITEMDATENAMEINVOICEGOODSTYPEPRQTYUNITTOTAL
2125/05/2023CR-1000FRVG-1000ATRAM1GR55.0022.001,210.00
3225/05/2023CR-1000FRVG-1000ATRAM2PO14.0033.00462.00
4325/05/2023CR-1000FRVG-1000ATRAM1SO10.0044.00440.00
5TOTAL25/05/2023CR-1000FRVG-10002,112.00
6125/05/2023CR-1001FRVG-1001ATRAM1GR10.0022.00220.00
7225/05/2023CR-1001FRVG-1001ATRAM2PO10.0033.00330.00
8TOTAL25/05/2023CR-1001FRVG-1001550.00
9125/05/2023CR-1000FRVG-1002ATRAM1GR55.0022.001,210.00
10TOTAL25/05/2023CR-1000FRVG-10021,210.00
11125/05/2023CR-1000FRVG-1003ATRAM1GR55.0010.00550.00
12TOTAL25/05/2023CR-1000FRVG-1003550.00
BUYING



KLL.xlsm
ABCDEFGHIJ
1ITEMDATENAMEINVOICEGOODSTYPEPRQTYUNITTOTAL
2125/05/2023CR-1000STVG-1000ATRAM1MTR5.0025.00125.00
3225/05/2023CR-1000STVG-1000ATRAM2PO4.0035.00140.00
4325/05/2023CR-1000STVG-1000ATRAM1SO2.0045.0090.00
5TOTAL25/05/2023CR-1000STVG-1000355.00
6125/05/2023CR-1001STVG-1001ATRAM1GR2.0023.0046.00
7225/05/2023CR-1001STVG-1001ATRAM2PO2.0035.0070.00
8TOTAL25/05/2023CR-1001STVG-1001116.00
9125/05/2023CR-1001STVG-1002ATRAM1GR2.0023.0046.00
10TOTAL25/05/2023CR-1001STVG-100246
SALES


KLL.xlsm
ABCDE
1DATEINFORMATIONDEBITCREDITBALANCE
225/05/2023BUYING GOODS INVOICE NUMBER FRVG-10002112-2112
325/05/2023BUYING GOODS INVOICE NUMBER FRVG-10021200-3312
425/05/2023SALES GOODS INVOICE NUMBER STVG-1000365-2947
5TOTAL3653312-2947
CR-1000


KLL.xlsm
ABCDE
1DATEINFORMATIONDEBITCREDITBALANCE
225/05/2023BUYING GOODS INVOICE NUMBER FRVG-1001545-545
325/05/2023SALES GOODS INVOICE NUMBER STVG-1001116-429
425/05/2023SALES GOODS INVOICE NUMBER STVG-100246-383
5TOTAL162545-383
CR-1001


before brings data

KLL.xlsm
ABCDEFGHI
1ITEMNAMEINVOICE NOBUYING AMOUNT SALES AMOUNT CREDIT DEBITDIFFERENCE CREDITDIFFERENCE DEBIT
2
3
4
5
6
7
8
REPORT



result should be
KLL.xlsm
ABCDEFGHI
1ITEMNAMEINVOICE NOBUYING AMOUNT SALES AMOUNT CREDIT DEBITDIFFERENCE CREDITDIFFERENCE DEBIT
21CR-1000FRVG-10002,112.002,112.000.000.00
32CR-1000FRVG-10021,210.001,200.0010.000.00
43CR-1000FRVG-1003550.00550.000.00
54CR-1000STVG-1000355.00365.000.00-10.00
65CR-1001FRVG-1001550.00545.005.000.00
76CR-1001STVG-1001116.00116.000.000.00
87CR-1001STVG-100246.0046.000.000.00
REPORT
Cell Formulas
RangeFormula
H2:I8H2=D2-F2

I hope somebody help .
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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