extraction invoices detail for TOTAL row for each sheet individual

[KalilMe]

Hi
I would extract invoices details for each sheet based on TOTAL row.
so should extract columns B:D ,J for TOTAL row for each sheet individual and populate data from row2 in A:D based on sales sheet ,F:I based on buying sheet and insert TOTAL row to sum amounts in columns D,I in extraction sheet.
and will increase data in SALES,BUYING sheet. if the vba is possible then will should clear data in EXTRACTION sheet and replace data with data have already copied .

EX.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	NAME	INVOICE	GOODS	TYPE	PR	QTY	UNIT	TOTAL
2	1	25/05/2023	CR-1000	FRVG-1000	ATR	AM1	GR	55.00	22.00	1,210.00
3	2	25/05/2023	CR-1000	FRVG-1000	ATR	AM2	PO	14.00	33.00	462.00
4	3	25/05/2023	CR-1000	FRVG-1000	ATR	AM1	SO	10.00	44.00	440.00
5	TOTAL	25/05/2023	CR-1000	FRVG-1000						2,112.00
6	1	25/05/2023	CR-1001	FRVG-1001	ATR	AM1	GR	10.00	22.00	220.00
7	2	25/05/2023	CR-1001	FRVG-1001	ATR	AM2	PO	10.00	33.00	330.00
8	TOTAL	25/05/2023	CR-1001	FRVG-1001						550.00
SALES

EX.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	NAME	INVOICE	GOODS	TYPE	PR	QTY	UNIT	TOTAL
2	1	25/05/2023	CR-1000	STVG-1000	ATR	AM1	MTR	5.00	25.00	125.00
3	2	25/05/2023	CR-1000	STVG-1000	ATR	AM2	PO	4.00	35.00	140.00
4	3	25/05/2023	CR-1000	STVG-1000	ATR	AM1	SO	2.00	45.00	90.00
5	TOTAL	25/05/2023	CR-1000	STVG-1000						355.00
6	1	25/05/2023	CR-1001	STVG-1001	ATR	AM1	GR	2.00	23.00	46.00
7	2	25/05/2023	CR-1001	STVG-1001	ATR	AM2	PO	2.00	35.00	70.00
8	TOTAL	25/05/2023	CR-1001	STVG-1001						116.00
BUYING

EX.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	NAME	SALES INVOICES	TOTAL		DATE	NAME	SALES INVOICES	TOTAL
2
3
4
EXTRACTION

what I want it

EX.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	NAME	SALES INVOICES	TOTAL		DATE	NAME	SALES INVOICES	TOTAL
2	25/05/2023	CR-1000	FRVG-1000	2,112.00		25/05/2023	CR-1000	STVG-1000	355.00
3	25/05/2023	CR-1001	FRVG-1001	550.00		25/05/2023	CR-1001	STVG-1001	116.00
4	TOTAL			2,662.00		TOTAL			471.00
EXTRACTION

thanks in advance

 

[alansidman]

Power Query Solution. Once Close and Loaded to Excel, you can split the table as needed.

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Append =Table.Combine({Source1, Source2}),
    TotalRows=Table.SelectRows(Append, each ([ITEM] = "TOTAL")),
    #"Removed Other Columns" = Table.SelectColumns(TotalRows,{"DATE", "NAME", "INVOICE", "TOTAL"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"DATE", type date}, {"NAME", type text}, {"INVOICE", type text}, {"TOTAL", Currency.Type}})
in
    #"Changed Type"

DATE	NAME	INVOICE	TOTAL
5/25/2023	CR-1000	FRVG-1000	2112
5/25/2023	CR-1001	FRVG-1001	550
5/25/2023	CR-1000	STVG-1000	355
5/25/2023	CR-1001	STVG-1001	116

 

[KalilMe]

I don't post combining two sheets in one range as you did it!
and where is TOTAL row?

 

[alansidman]

As I indicated, you may split these in Native Excel and add the total row. Those are basic excel functions that I did not feel you needed help. Copy and Paste and then Sum.

 

[KalilMe]

So in this case PQ is useless!
I want to do it automatically.

 

[RudRud]

工作簿2
	A	B	C	D	E	F	G	H	I
1	DATE	NAME	SALES INVOICES	TOTAL		DATE	NAME	SALES INVOICES	TOTAL
2	5/25/23	CR-1000	FRVG-1000	2112		5/25/23	CR-1000	STVG-1000	355
3	5/25/23	CR-1001	FRVG-1001	550		5/25/23	CR-1001	STVG-1001	116
4	TOTAL			2662		TOTAL			471
EXTRACTION

Cell Formulas
Range	Formula
D4,I4	D4	=SUM(D2:D3)

Please give below code for a shot, right click extraction sheet -> view code -> paste below code and run the code (f5)

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Set ws = Sheets("EXTRACTION")
Dim a As Variant
'Dim dict As New Dictionary ' For mac
Dim dict As object
Set dict = New Scripting.Dictionary
Dim i%, k%, lrow%
dict.CompareMode = vbTextCompare
Dim sht As Worksheet
ReDim b(1 To 5000, 1 To 5)

ws.[a2:i10000].ClearContents ' clear all text from a2 to i100000
ws.[a2:a10000].Interior.Color = xlNone ' clear total color range a2
ws.[f2:f10000].Interior.Color = xlNone 'clear total color range f2

For Each sht In Sheets(Array("SALES", "BUYING")) 'loop through sales and buying sheets
        a = sht.Range("a2:j" & sht.Cells(Rows.Count, "A").End(xlUp).Row).Value 'store into array
    For i = 1 To UBound(a, 1) 'loop through array to find existing dict
            If Not dict.Exists(a(i, 3)) Then
            k = k + 1
            dict.Add a(i, 3), k
            b(k, 1) = a(i, 2)
             b(k, 2) = a(i, 3)
             b(k, 3) = a(i, 4)
             b(k, 4) = a(i, 10)
            ElseIf a(i, 1) <> "TOTAL" Then
                b(dict(a(i, 3)), 4) = b(dict(a(i, 3)), 4) + a(i, 10)
            End If
    Next
   
    If sht.Name = "SALES" Then
          ws.[a2].Resize(UBound(b, 1), UBound(b, 2)).Value = b
          lrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
          ws.Cells(lrow + 1, "A").Value = "TOTAL"
          ws.Cells(lrow + 1, "A").Interior.Color = RGB(113, 129, 186)
          ws.Cells(lrow + 1, "d").Formula = "=sum(d2:d" & lrow & ")"
    ElseIf sht.Name = "BUYING" Then
         ws.[f2].Resize(UBound(b, 1), UBound(b, 2)).Value = b
         lrow = ws.Cells(Rows.Count, "f").End(xlUp).Row
         ws.Cells(lrow + 1, "f").Value = "TOTAL"
         ws.Cells(lrow + 1, "f").Interior.Color = RGB(113, 129, 186)
          ws.Cells(lrow + 1, "i").Formula = "=sum(I2:i" & lrow & ")"
    End If

dict.RemoveAll
ReDim b(1 To 5000, 1 To 5)
k = 0
Next


End Sub

 

[KalilMe]

@RudRud
awesome !
I believe in my case needs vba.
it works as I expected .
thank you so much .

 

[RudRud]

@RudRud
awesome !
I believe in my case needs vba.
it works as I expected .
thank you so much .

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us