collection columns and delete other for 8000 rows

[Maklil]

Hi

I search for macro to merge duplicates items for 8000 rows . so I want merge duplicates items based on column B for each sheet individually .

in sheet purchase ,sell just I want merging column B,E .so in sheet output should merge duplicates items , values for sheet PURCHSE and put in column C. as to sheet SELL should merge duplicates items , values and put in column D and show the BALANCE in column E as I put the formula . every time when run the macro should clear data from row2 before brings data .

items.xlsx
	A	B	C	D	E
1	DATE	ITEM	CUSTOMER	INV NO	QTY
2	11/11/19	ITTT-100/AS-1	CSS-100	INV-A123	200
3	12/11/19	ITTT-100/AS-2	CSS-101	INV-A124	300
4	13/11/19	ITTT-100/AS-3	CSS-102	INV-A125	400
5	14/11/19	ITTT-100/AS-4	CSS-103	INV-A126	500
6	15/11/19	ITTT-100/AS-5	CSS-104	INV-A127	600
7	16/11/19	ITTT-100/AS-6	CSS-105	INV-A128	700
8	17/11/19	ITTT-100/AS-7	CSS-100	INV-A129	800
9	18/11/19	ITTT-100/AS-4	CSS-107	INV-A130	900
10	19/11/19	ITTT-100/AS-5	CSS-108	INV-A131	1000
11	20/11/19	ITTT-100/AS-6	CSS-109	INV-A132	1100
12	21/11/19	ITTT-100/AS-7	CSS-110	INV-A133	1200
13	22/11/19	ITTT-100/AS-1	CSS-111	INV-A134	1300
14	23/11/19	ITTT-100/AS-2	CSS-112	INV-A135	1400
15	24/11/19	ITTT-100/AS-3	CSS-102	INV-A136	1500
16	25/11/19	ITTT-100/AS-8	CSS-107	INV-A137	1600
17	26/11/19	ITTT-100/AS-10	CSS-108	INV-A138	1601
PURCHSE

items.xlsx
	A	B	C	D	E
1	DATE	ITEM	CLIENT	INV NO	QTY
2	30/11/19	ITTT-100/AS-8	CLS-100	INV-AT10	100
3	01/12/19	ITTT-100/AS-5	CLS-101	INV-AT11	120
4	02/12/19	ITTT-100/AS-6	CLS-102	INV-AT12	140
5	03/12/19	ITTT-100/AS-4	CLS-103	INV-AT13	160
6	04/12/19	ITTT-100/AS-5	CLS-104	INV-AT14	180
7	05/12/19	ITTT-100/AS-5	CLS-105	INV-AT15	200
8	06/12/19	ITTT-100/AS-7	CLS-106	INV-AT16	220
9	07/12/19	ITTT-100/AS-4	CLS-107	INV-AT17	240
10	08/12/19	ITTT-100/AS-5	CLS-108	INV-AT18	260
11	09/12/19	ITTT-100/AS-6	CLS-109	INV-AT19	280
12	10/12/19	ITTT-100/AS-7	CLS-110	INV-AT20	300
13	12/12/19	ITTT-100/AS-2	CLS-112	INV-AT22	340
14	14/12/19	ITTT-100/AS-8	CLS-114	INV-AT24	380
15	15/12/19	ITTT-100/AS-9	CLS-115	INV-AT25	381
SELL

items.xlsx
	A	B	C	D	E
1	ITEM	BRAND	PURCHASE	SELL	BALANCE
2	1	ITTT-100/AS-1	1500	-	1500
3	2	ITTT-100/AS-2	1700	340	1360
4	3	ITTT-100/AS-3	1900	-	1900
5	4	ITTT-100/AS-4	1400	400	1000
6	5	ITTT-100/AS-5	1600	500	1100
7	6	ITTT-100/AS-6	1800	420	1380
8	7	ITTT-100/AS-7	2000	300	1700
9	8	ITTT-100/AS-8	-	480	-480
10	9	ITTT-100/AS-9	-	381	-381
11	10	ITTT-100/AS-10	1061		1061
OUTPUT

Cell Formulas
Range	Formula
E2:E11	E2	=C2-D2

thanks

 

[mohadin]

Hi
Perhaps

Sub test()
    Dim a, itm
    Dim i&
    a = Sheets("PURCHSE").Cells(1).CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If a(i, 2) <> 0 Then
                If Not .exists(a(i, 2)) Then
                    .Add a(i, 2), Array(a(i, 2), a(i, 5), 0)
                Else
                      w = .Item(a(i, 2))
                      w(1) = w(1) + a(i, 5)
                      .Item(a(i, 2)) = w
                End If
            End If
        Next
         a = Sheets("SEll").Cells(1).CurrentRegion
         For i = 2 To UBound(a)
            If a(i, 2) <> 0 Then
                If .exists(a(i, 2)) Then
                     w = .Item(a(i, 2))
                      w(2) = w(2) + a(i, 5)
                      .Item(a(i, 2)) = w
                Else
                  .Add a(i, 2), Array(a(i, 2), 0, a(i, 5))
                End If
            End If
        Next
        k = .Count:  itm = .items
      End With
        With Sheets("OUTPUT")
        .Cells(1).CurrentRegion.ClearContents
        .Cells(1, 1).Resize(, 5) = Array("Item", "BRAND", "PURCHASE", "SELL", "BALANCE")
        .Cells(2, 1).Resize(k) = Application.Evaluate("row(1:" & k & ")")
        .Cells(2, 2).Resize(k, 3) = Application.Index(itm, 0, 0)
        Cells(2, 5).Resize(k).FormulaR1C1 = "=RC[-2]-RC[-1]"
    End With
End Sub

 

[Maklil]

Hi
thanks , but I need fixing some problems .
1- seem the sheet output should be active . if it's not will implement any active sheet and will occures big choas and error mismatch in this line

W(1) = W(1) + a(i, 5)

so I want avoiding this problem if I select any sheet will show data in sheet OUTPUT without any problem
2- variables need declaration W,K . it gives error varibale not defined
3- I don't want showing formula in column BALANCE just as value.

 

[mohadin]

All right a typo

I don't want showing formula in column BALANCE just as value.

Then try

Sub test()
    Dim a, itm, k, w
    Dim i&
    a = Sheets("PURCHSE").Cells(1).CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If a(i, 2) <> 0 Then
                If Not .exists(a(i, 2)) Then
                    .Add a(i, 2), Array(a(i, 2), a(i, 5), 0, a(i, 5))
                Else
                      w = .Item(a(i, 2))
                      w(1) = w(1) + a(i, 5)
                      w(3) = w(1) - w(2)
                      .Item(a(i, 2)) = w
                End If
            End If
        Next
         a = Sheets("SEll").Cells(1).CurrentRegion
         For i = 2 To UBound(a)
            If a(i, 2) <> 0 Then
                If .exists(a(i, 2)) Then
                     w = .Item(a(i, 2))
                      w(2) = w(2) + a(i, 5)
                       w(3) = w(1) - w(2)
                      .Item(a(i, 2)) = w
                Else
                  .Add a(i, 2), Array(a(i, 2), 0, a(i, 5), -a(i, 5))
                End If
            End If
        Next
        k = .Count:  itm = .items
      End With
        With Sheets("OUTPUT")
        .Cells(1).CurrentRegion.ClearContents
        .Cells(1, 1).Resize(, 5) = Array("Item", "BRAND", "PURCHASE", "SELL", "BALANCE")
        .Cells(2, 1).Resize(k) = Application.Evaluate("row(1:" & k & ")")
        .Cells(2, 2).Resize(k, 4) = Application.Index(itm, 0, 0)
'        .Cells(2, 5).Resize(k).FormulaR1C1 = "=RC[-2]-RC[-1]"
    End With
End Sub

 

[Huuktkt]

SQL Trong excel sẽ xử lý ok

 

[alansidman]

An alternative means is with Power Query

1. Load purchases and Group

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ITEM"}, {{"Purchase", each List.Sum([QTY]), type number}})
in
    #"Grouped Rows"

2. Load Sales and Group

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ITEM"}, {{"Sales", each List.Sum([QTY]), type number}})
in
    #"Grouped Rows"

3. Join the two tables with a full outer join and subtract sales from purchases

let
    Source = Table.NestedJoin(Table1, {"ITEM"}, Table2, {"ITEM"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ITEM", "Sales"}, {"ITEM.1", "Sales"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,0,Replacer.ReplaceValue,{"Purchase"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Sales"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [Purchase] - [Sales], type number)
in
    #"Inserted Subtraction"

.4. Any updates to either table will automatically update the results when you select Refresh All on the Data Tab.

 

[Maklil]

@mohadin thanks, but can you help me sorting data based on ID in column B in sheet OUTPUT from small to big like this,please?

ITTT-100/AS-1

ITTT-100/AS-2

ITTT-100/AS-3

ITTT-100/AS-4

ITTT-100/AS-5

ITTT-100/AS-6

ITTT-100/AS-7

ITTT-100/AS-8

ITTT-100/AS-9

ITTT-100/AS-10

ITTT-100/AS-11

ITTT-100/AS-12

ITTT-100/AS-13

ITTT-100/AS-14

ITTT-100/AS-15

ITTT-100/AS-16

ITTT-100/AS-17

ITTT-100/AS-18

 

[Maklil]

@alansidman thanks but step 3 for PQ3 when join two tables gives error name Table1 , how ?
should also shows error for PQ1 but it doesn't , this means to be right in PQ1

 

[alansidman]

What is the error message? Don't know what your issue is. Are all field names in your tables the same as in your example.

 

[Maklil]

What is the error message?

I said

gives error name Table1