Merging duplicates items after words contains colon based on different price for the same ID

[abdo meghari]

Hello,
I would merge quantity in column E for duplicates BRANDS in column D based on column C for each range contains colon for each name MOVEMENT word is existed after colon .
, but when merge QTY should be based on the same price in unit price in column F . so you will note some the same duplicates IDs contain different price , so don't merge QTY for the same duplicates IDs contain different price .
when merge data should brings column C:F from INVOICES sheet and insert TOTAL column and calculate as the formula is existed .
every time when run the macro should replace new data with old data, also should sort data for each range based on column C from small to big based on numbers . .
last thing I have many ranges contains words and suffix :
I realized error about my topic . should merge same ID based on same price, not different price.
I can't correct my topic, sorry about this mistake!!

split brands.xlsm
	A	B	C	D	E	F	G
2				MOVEMENT : PURCHASING
3	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	2024.01.02	114	1284	GC 1200R20 AZ0026 CHI	80	895.000	242,560.000
5			1285	GC 1200R20 AZ0183 CHI	40	925.000
6			1385	GC 315/80R22.5 AT161 CHI	20	735.000
7			1287	GC 315/80R22.5 AZ126 CHI	20	735.000
8			1294	GC 315/80R22.5 AZ188 CHI	20	745.000
9			1241	BS 1200R20 G580 JAP	40	2,035.000
10			1227	BS 215/70R15C R623 THI	8	425.000
11			1221	BS 205/70R15C R623 THI	12	405.000
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.01.09	121	1287	GC 315/80R22.5 AZ126 CHI	14	735.000	10,290.000
14	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
15	2024.01.10	125	1227	BS 215/70R15C R623 THI	2	425.000	850.000
16	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
17	2024.01.20	148	1287	GC 315/80R22.5 AZ126 CHI	26	735.000	19,110.000
18	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
19	2024.01.20	149	1221	BS 205/70R15C R623 THI	50	405.000	391,650.000
20			1227	BS 215/70R15C R623 THI	12	425.000
21			1241	BS 1200R20 G580 JAP	180	2,035.000
22	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
23	2024.03.30	217	1285	GC 1200R20 AZ0183 CHI	10	1,225.000	12,250.000
24	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
25	2024.04.09	226	1227	BS 215/70R15C R623 THI	4	544.000	2,176.000
26	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
27	2024.04.14	230	1285	GC 1200R20 AZ0183 CHI	40	1,205.000	81,950.000
28			1284	GC 1200R20 AZ0026 CHI	30	1,125.000
29	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
30	2024.04.17	236	1284	GC 1200R20 AZ0026 CHI	20	1,125.000	46,600.000
31			1285	GC 1200R20 AZ0183 CHI	20	1,205.000
32				MOVEMENT : SELLING
33	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
34	2024.01.24	561	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
35	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
36	2024.01.24	562	1306	BS 750R16 R230 JAP	2	780.000	1,560.000
37	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
38	2024.01.24	563	1306	BS 750R16 R230 JAP	2	780.000	3,100.000
39			1305	BS 700R16 R230 JAP	2	770.000
40
41	SUMMARY
42	TOTAL PURCHASE	807,436.000
43	TOTAL SELLING	12,410.000
INVOICES

result

split brands.xlsm
	A	B	C	D	E	F
2			MOVEMENT : PURCHASING
3	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	1	1221	BS 205/70R15C R623 THI	62	405.000	25,110.000
5	2	1227	BS 215/70R15C R623 THI	22	425.000	9,350.000
6	3	1227	BS 215/70R15C R623 THI	4	544.000	2,176.000
7	4	1241	BS 1200R20 G580 JAP	220	2,035.000	447,700.000
8	5	1284	GC 1200R20 AZ0026 CHI	80	895.000	71,600.000
9	6	1284	GC 1200R20 AZ0026 CHI	50	1,125.000	56,250.000
10	7	1285	GC 1200R20 AZ0183 CHI	40	925.000	37,000.000
11	8	1285	GC 1200R20 AZ0183 CHI	10	1,225.000	12,250.000
12	9	1285	GC 1200R20 AZ0183 CHI	60	1,205.000	72,300.000
13	10	1385	GC 315/80R22.5 AT161 CHI	20	735.000	14,700.000
14	11	1294	GC 315/80R22.5 AZ188 CHI	20	745.000	14,900.000
15	12	1287	GC 315/80R22.5 AZ126 CHI	60	735.000	44,100.000
16			MOVEMENT : SELLING
17	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
18	1	1305	BS 700R16 R230 JAP	2	770.000	1,540.000
19	2	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
20	3	1306	BS 750R16 R230 JAP	4	780.000	3,120.000
21
22	SUMMARY
23	TOTAL PURCHASE	807,436.000
24	TOTAL SELLING	12,410.000
RESULT

Cell Formulas
Range	Formula
F18:F20,F4:F15	F4	=D4*E4

I hope to don't make any mistake .

 

[alansidman]

With Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([DATE] <> "DATE")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"CODE", "BRAND", "UNIT PRICE"}, {{"Quantity", each List.Sum([QTY]), type nullable number}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([CODE] <> null))
in
    #"Filtered Rows1"

 

[abdo meghari]

Thanks
this is not what I want . should show for every range alone as I said

I would merge quantity in column E for duplicates BRANDS in column D based on column C for each range contains colon for each name MOVEMENT word is existed after colon .

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column4],"MOVEMENT") then [Column4] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"CODE", "BRAND", "UNIT PRICE", "MOVEMENT : PURCHASING"}, {{"Quantity", each List.Sum([QTY]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([CODE] <> "CODE")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"MOVEMENT : PURCHASING", "MOVEMENT"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([CODE] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows2",{"CODE", "BRAND", "UNIT PRICE", "Quantity", "MOVEMENT"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([CODE] <> null))
in
    #"Filtered Rows1"

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub Get_Results()
  Dim d As Object
  Dim a As Variant
  Dim wsI As Worksheet, wsR As Worksheet
  Dim fr As Long, lr As Long, vlr As Long, i As Long, nr As Long
  Dim s As String
  
  Set wsI = Sheets("INVOICES")
  Set wsR = Sheets("RESULT")
  wsR.UsedRange.EntireColumn.Delete
  Set d = CreateObject("Scripting.Dictionary")
  nr = 2
  With wsI.Columns("D")
    fr = .Find(What:="MOVEMENT :*", LookAt:=xlWhole).Row
    vlr = .Cells(.Rows.Count).End(xlUp).Row
    Do
      lr = .Find(What:="MOVEMENT :*", After:=.Cells(fr), LookAt:=xlWhole).Row - 1
      If lr < fr Then lr = vlr
      a = wsI.Range("C" & fr & ":F" & lr).Value
      For i = 2 To UBound(a)
        If a(i, 1) <> "CODE" Then
          s = a(i, 1) & ";" & a(i, 2) & ";;" & a(i, 4)
          d(s) = d(s) + a(i, 3)
        End If
      Next i
      wsI.Range("B" & fr).Resize(2, 6).Copy Destination:=wsR.Range("A" & nr)
      wsR.Range("A" & nr + 1).Value = "ITEM"
      With wsR.Range("B" & nr + 2).Resize(d.Count)
        .Value = Application.Transpose(d.Keys)
        .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
        .Offset(, 2).Value = Application.Transpose(d.Items)
        .Offset(, 4).FormulaR1C1 = "=RC[-2]*RC[-1]"
        .Resize(, 5).Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
      End With
      nr = nr + d.Count + 1
      fr = .Find(What:="MOVEMENT :*", After:=.Cells(fr), LookAt:=xlWhole).Row
      d.RemoveAll
    Loop Until fr < lr
  End With
  wsI.Range("A" & Rows.Count).End(xlUp).CurrentRegion.Copy Destination:=wsR.Range("A" & nr + 2)
  wsR.Range("B" & nr + 3).Resize(2).NumberFormat = "#,##0.000"
  wsR.Columns("E:F").NumberFormat = "#,##0.000"
  With wsR.Range("A3:A" & wsR.Range("C" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=IFERROR(R[-1]C+1,1)"
    .Value = .Value
  End With
End Sub

 

[abdo meghari]

Thanks Alan.
may you attach file works for you?
actually I face problem , will shows some errors !
I'm not sure why !

 

[abdo meghari]

Hi Peter again .
I hope you're fine .
I'm not sure why the code just run with MOVEMENT: SELLING and ignore MOVEMENT: PURCHASEING totally!
do you have any idea what's the problem?

 

[Peter_SSs]

do you have any idea what's the problem?

No, I copied your XL2BB 'INVOICES' data to my blank INVOICES sheet ..

abdo meghari.xlsm
	A	B	C	D	E	F	G
1
2				MOVEMENT : PURCHASING
3	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	2024.01.02	114	1284	GC 1200R20 AZ0026 CHI	80	895	242560
5			1285	GC 1200R20 AZ0183 CHI	40	925
6			1385	GC 315/80R22.5 AT161 CHI	20	735
7			1287	GC 315/80R22.5 AZ126 CHI	20	735
8			1294	GC 315/80R22.5 AZ188 CHI	20	745
9			1241	BS 1200R20 G580 JAP	40	2035
10			1227	BS 215/70R15C R623 THI	8	425
11			1221	BS 205/70R15C R623 THI	12	405
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.01.09	121	1287	GC 315/80R22.5 AZ126 CHI	14	735	10290
14	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
15	2024.01.10	125	1227	BS 215/70R15C R623 THI	2	425	850
16	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
17	2024.01.20	148	1287	GC 315/80R22.5 AZ126 CHI	26	735	19110
18	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
19	2024.01.20	149	1221	BS 205/70R15C R623 THI	50	405	391650
20			1227	BS 215/70R15C R623 THI	12	425
21			1241	BS 1200R20 G580 JAP	180	2035
22	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
23	2024.03.30	217	1285	GC 1200R20 AZ0183 CHI	10	1225	12250
24	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
25	2024.04.09	226	1227	BS 215/70R15C R623 THI	4	544	2176
26	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
27	2024.04.14	230	1285	GC 1200R20 AZ0183 CHI	40	1205	81950
28			1284	GC 1200R20 AZ0026 CHI	30	1125
29	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
30	2024.04.17	236	1284	GC 1200R20 AZ0026 CHI	20	1125	46600
31			1285	GC 1200R20 AZ0183 CHI	20	1205
32				MOVEMENT : SELLING
33	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
34	2024.01.24	561	1306	BS 750R16 R230 JAP	10	775	7750
35	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
36	2024.01.24	562	1306	BS 750R16 R230 JAP	2	780	1560
37	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
38	2024.01.24	563	1306	BS 750R16 R230 JAP	2	780	3100
39			1305	BS 700R16 R230 JAP	2	770
40
41	SUMMARY
42	TOTAL PURCHASE	807436
43	TOTAL SELLING	12410
INVOICES

.. and created a 'RESULT' sheet and ran the code. This is the result.
I did manually adjust the column widths afterwards on this sheet but the code could do that too if required.

abdo meghari.xlsm
	A	B	C	D	E	F
1
2			MOVEMENT : PURCHASING
3	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	1	1221	BS 205/70R15C R623 THI	62	405.000	25,110.000
5	2	1227	BS 215/70R15C R623 THI	22	425.000	9,350.000
6	3	1227	BS 215/70R15C R623 THI	4	544.000	2,176.000
7	4	1241	BS 1200R20 G580 JAP	220	2,035.000	447,700.000
8	5	1284	GC 1200R20 AZ0026 CHI	80	895.000	71,600.000
9	6	1284	GC 1200R20 AZ0026 CHI	50	1,125.000	56,250.000
10	7	1285	GC 1200R20 AZ0183 CHI	40	925.000	37,000.000
11	8	1285	GC 1200R20 AZ0183 CHI	10	1,225.000	12,250.000
12	9	1285	GC 1200R20 AZ0183 CHI	60	1,205.000	72,300.000
13	10	1287	GC 315/80R22.5 AZ126 CHI	60	735.000	44,100.000
14	11	1294	GC 315/80R22.5 AZ188 CHI	20	745.000	14,900.000
15	12		MOVEMENT : SELLING
16	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
17	1	1305	BS 700R16 R230 JAP	2	770.000	1,540.000
18	2	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
19	3	1306	BS 750R16 R230 JAP	4	780.000	3,120.000
20
21	SUMMARY
22	TOTAL PURCHASE	807,436.000
23	TOTAL SELLING	12,410.000
RESULT

Cell Formulas
Range	Formula
F4:F14,F17:F19	F4	=D4*E4

 

[Peter_SSs]

Something to try. There are three lines in the code similar to this

In each of those 3 lines, try removing the 3 yellow characters (space colon and asterisk) and also change xlWhole to xlPart

 

[abdo meghari]

My apologies Peter !
now I know my bad!
I changed the range to table when test as @alansidman suggest by PQ that's why the code doesn't work well.
your code works excellently.
just I would fix borders problem. I notice the code will not create borders around the cells even if I add it manually the code will delete it. may you fix this problem ,please?