copy values after TOTAL row and subtract discount from it for two sheets

[KalilMe]

Hello
I have two sheets purchase ,debit
sheet PURCHASE contains invoices numbers in column B and summing values for each invoice by TOTAL in lastrow . sheets DEBIT also contains invoices numbers in column B . sheet DISCOUNT this is important when copy valuse from sheet DISCOUNT to sheets purchase, DEBIT . sheet PURCHASE should copy values to after TOTAL row from sheet DISCOUNT ( should summing values and merge dupilcates invoices in sheet DISCOUNT when copy to sheet PURCHASE ) and add NET row to subtract TOTAL from DISCOUNT and when show row NET but in sheet DEBIT should add after last row contains the same invoice no in column B with repeat client name and put word DISCOUNT in column B and put the value in column E .

Result (1) (1).xlsx
	A	B	C	D	E	F	G	H	I	J
1	ITEM	INVOICE NO	ORDER NO	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	1	INV00-001	DEL00-001	COR-FF1	FR	BANANA	TT	400.00	230.00	TRY 92,000.00
3	2	INV00-001	DEL00-002	COR-FF2	FR	APPLE	LL	100.00	234.00	TRY 23,400.00
4	3	INV00-001	DEL00-003	COR-FF3	FR	PEAR	NN	10.00	233.00	TRY 2,330.00
5	TOATL									TRY 117,730.00
6	1	INV00-002	DEL00-004	COR-FF4	FR	BANANA	QQ	20.00	300.00	TRY 6,000.00
7	TOATL									TRY 6,000.00
8	1	INV00-003	DEL00-005	COR-FF5	VEG	TOMATO	SS	22.00	200.00	TRY 4,400.00
9	TOATL									TRY 4,400.00
10	1	INV00-004	DEL00-006	COR-FF6	VEG1	TOMATO	AA	22.00	123.00	TRY 2,706.00
11	2	INV00-004	DEL00-007	COR-FF7	VEG2	TOMATO	AA	33.00	234.00	TRY 7,722.00
12	TOATL									TRY 10,428.00
PURCHASE

Cell Formulas
Range	Formula
J10:J11,J8,J6,J2:J4	J2	=I2*H2
J5	J5	=SUM(J2:J4)
J7,J9	J7	=J6
J12	J12	=SUM(J10:J11)

Result (1) (1).xlsx
	A	B	C	D	E
1	ITEM	INVOICE NO	CLIENT	DEBIT	CREDIT
2	1	INV00-001	KALIL1	200000	1000
3	2	INV00-002	KALIL2
4	3	INV00-003	KALIL3	100000	1000
5	4	INV00-004	KALIL4
6	5	INV00-005	KALIL5	250000	1000
7	6	INV00-006	KALIL6
8
DEBIT

Result (1) (1).xlsx
	A	B	C	D	E	F	G	H	I	J
1	ITEM	INVOICE NO	DISCOUNT	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	1	INV00-001	DIS00-001	COR-FF1	FR	BANANA	TT	400.00	220.00	TRY 88,000.00
3	2	INV00-001	DIS00-002	COR-FF2	FR	APPLE	LL	100.00	213.00	TRY 21,300.00
4	3	INV00-002	DIS00-003	COR-FF4	FR	BANANA	QQ	20.00	220.00	TRY 4,400.00
DISCOUNT

Cell Formulas
Range	Formula
J2:J4	J2	=I2*H2

result in sheet PURCHASE

Result (1) (1).xlsx
	A	B	C	D	E	F	G	H	I	J
1	ITEM	INVOICE NO	ORDER NO	CO-IT	FOOD	TT-MMN	ORT-WW	QTY	PRICE	TOTAL
2	1	INV00-001	DEL00-001	COR-FF1	FR	BANANA	TT	400.00	230.00	TRY 92,000.00
3	2	INV00-001	DEL00-002	COR-FF2	FR	APPLE	LL	100.00	234.00	TRY 23,400.00
4	3	INV00-001	DEL00-003	COR-FF3	FR	PEAR	NN	10.00	233.00	TRY 2,330.00
5	TOTAL									TRY 117,730.00
6	DISCOUNT									TRY 109,300.00
7	NET									TRY 8,430.00
8	1	INV00-002	DEL00-004	COR-FF4	FR	BANANA	QQ	20.00	300.00	TRY 6,000.00
9	TOTAL									TRY 6,000.00
10	DISCOUNT									TRY 4,400.00
11	NET									TRY 1,600.00
12	1	INV00-003	DEL00-005	COR-FF5	VEG	TOMATO	SS	22.00	200.00	TRY 4,400.00
13	TOTAL									TRY 4,400.00
14	1	INV00-004	DEL00-006	COR-FF6	VEG1	TOMATO	AA	22.00	123.00	TRY 2,706.00
15	2	INV00-004	DEL00-007	COR-FF7	VEG2	TOMATO	AA	33.00	234.00	TRY 7,722.00
16	TOTAL									TRY 10,428.00
PURCHASE

Cell Formulas
Range	Formula
J14:J15,J12,J8,J2:J4	J2	=I2*H2
J5	J5	=SUM(J2:J4)
J7,J11	J7	=J5-J6
J16	J16	=SUM(J14:J15)

RESULT IN SHEET DEBIT

Result (1) (1).xlsx
	A	B	C	D	E
1	ITEM	INVOICE NO	CLIENT	DEBIT	CREDIT
2	1	INV00-001	KALIL1	200000	1000
3		DISCOUNT	KALIL1		TRY 109,300.00
4	2	INV00-002	KALIL2
5		DISCOUNT	KALIL2		TRY 4,400.00
6	3	INV00-003	KALIL3	100000	1000
7	4	INV00-004	KALIL4
8	5	INV00-005	KALIL5	250000	1000
9	6	INV00-006	KALIL6
DEBIT

 

[KalilMe]

I've found my bad ! as you see in picture1 the word TOTAL is wrong TOATL that's why doesn't show data correctly . but even that still the code has problem in sheet DEBIT I added some data in sheet DISCOUNT but really strange !! some invoices doesn't brings values from sheet DISCOUNT to sheet DEBIT for invoices(INV00-003,INV00-006) you can note that in sheet DEBIT.
I hope to see my file soon when you have free time.
TOTAL.xlsm

 

[bebo021999]

My faul!
Instead of creating dictionary from "DISCOUNT", I have worked with sheet PURCHASE to add items into dictionary!
Try again!

TOTAL.xlsm

drive.google.com

 

[KalilMe]

excellent !

seem to work very greatly , there is no problem so far and I hope continuing until I don't come back and upset you.

so I decided to post your code maybe the file will delete after time . just the others members take advantage from your code, of course after you .

many thanks for your assistance .

Option Explicit
Sub TEST()
Dim lr&, i&, j&, k&, c&, rng, arr(), dic As Object, key
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("DISCOUNT")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    rng = .Range("A2:J" & lr).Value
    For i = 1 To lr - 1
        If Not dic.exists(rng(i, 2)) And rng(i, 2) <> "" Then
            dic.Add rng(i, 2), rng(i, 10)
        Else
            dic(rng(i, 2)) = dic(rng(i, 2)) + rng(i, 10)
        End If
    Next
End With
With Sheets("PURCHASE")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:J" & lr).Value
    ReDim arr(1 To 1000000, 1 To 10)
    For i = 1 To UBound(rng)
        If rng(i, 2) <> "" Then
            k = k + 1
            For j = 1 To 10
                arr(k, j) = rng(i, j)
            Next
        ElseIf rng(i, 1) = "TOTAL" Then
            c = 0: k = k + 1: arr(k, 1) = rng(i, 1): arr(k, 10) = rng(i, 10)
            For Each key In dic.keys
                If rng(i - 1, 2) = key Then
                    c = c + 1
                    k = k + 1
                    arr(k, 1) = "DISCOUNT": arr(k, 10) = dic(key)
                    Exit For
                End If
            Next
            If c = 0 Then
                k = k + 1
                arr(k, 1) = "DISCOUNT"
            End If
            k = k + 1
            arr(k, 1) = "NET": arr(k, 10) = arr(k - 2, 10) - arr(k - 1, 10)
        End If
    Next
    .Range("A2:J1000000").ClearContents
    .Range("A2").Resize(k, 10).Value = arr
    .Columns("A:J").AutoFit
End With
ReDim arr(1 To 1000000, 1 To 10)
With Sheets("DEBIT")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:E" & lr).Value
    k = 0
    For i = 1 To UBound(rng)
        If rng(i, 2) <> "DISCOUNT" And rng(i, 2) <> "" Then
            k = k + 1
            For j = 1 To 5
                arr(k, j) = rng(i, j)
            Next
            For Each key In dic.keys
                If rng(i, 2) = key And dic(key) > 0 Then
                    k = k + 1
                    arr(k, 2) = "DISCOUNT": arr(k, 3) = rng(i, 3): arr(k, 5) = dic(key)
                    Exit For
                End If
            Next
        End If
    Next
    .Range("A2:E1000000").ClearContents
    .Range("A2").Resize(k, 5).Value = arr
End With
End Sub