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]

Here's my file with the Mcode

PQ-Movement.xlsx | Powered by Box

app.box.com

 

[Peter_SSs]

Try this version. As well as adding borders

• I have fixed a small issue where the Item numbers in column A were extending a little too far down in each section (except the last section)
• Autofitted the columns

Sub Get_Results_v2()
  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:=xlPart).Row
    vlr = .Cells(.Rows.Count).End(xlUp).Row
    Do
      lr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).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
        .Offset(, -1).Value = Evaluate("row(1:" & d.Count & ")")
        With .Cells(0, 0).Resize(d.Count + 1, 6)
          .BorderAround xlContinuous
          .Borders(xlInsideVertical).LineStyle = xlContinuous
          .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With
        .Cells(-1, 2).BorderAround xlContinuous
      End With
      nr = nr + d.Count + 1
      fr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).Row
      d.RemoveAll
    Loop Until fr < lr
  End With
  wsI.Range("A" & Rows.Count).End(xlUp).CurrentRegion.Copy Destination:=wsR.Range("A" & nr + 2)
  With wsR.Range("A" & Rows.Count).End(xlUp).CurrentRegion
    .BorderAround xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  End With
  wsR.Range("B" & nr + 3).Resize(2).NumberFormat = "#,##0.000"
  wsR.Columns("E:F").NumberFormat = "#,##0.000"
  wsR.UsedRange.Columns.AutoFit
End Sub

 

[abdo meghari]

Thanks Alan again
not really why work for you and not for me.
I downloaded your file and I entered in PQ editor to see with the Mcode and show me the same error , but before I enter in PQ editor will inform me the Mcode was written in a newer version.

 

[abdo meghari]

Thanks Peter again.

in the beginning and based on your first answering I don't check all of brands , just I check some BRANDS to see if the code works correctly because I expect should show for the rest of brands when show the others brands without problems , but when I check all of brands based on second answering I've found there is missed brand

CODE(1385) doesn't show at all !

may you check it, please?

 

[Peter_SSs]

CODE(1385) doesn't show at all !

Good catch - I had not noticed that. Try these changes (2 values shown in red towards the end of the code)

Sub Get_Results_v3()
  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:=xlPart).Row
    vlr = .Cells(.Rows.Count).End(xlUp).Row
    Do
      lr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).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
        .Offset(, -1).Value = Evaluate("row(1:" & d.Count & ")")
        With .Cells(0, 0).Resize(d.Count + 1, 6)
          .BorderAround xlContinuous
          .Borders(xlInsideVertical).LineStyle = xlContinuous
          .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With
        .Cells(-1, 2).BorderAround xlContinuous
      End With
      nr = nr + d.Count + 2
      fr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).Row
      d.RemoveAll
    Loop Until fr < lr
  End With
  wsI.Range("A" & Rows.Count).End(xlUp).CurrentRegion.Copy Destination:=wsR.Range("A" & nr + 1)
  With wsR.Range("A" & Rows.Count).End(xlUp).CurrentRegion
    .BorderAround xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  End With
  wsR.Range("B" & nr + 3).Resize(2).NumberFormat = "#,##0.000"
  wsR.Columns("E:F").NumberFormat = "#,##0.000"
  wsR.UsedRange.Columns.AutoFit
End Sub

 

[abdo meghari]

ok.
but will copy to the bottom twice when I run the macro every time .
what's the problem, is relating with your suggestion?

nr = nr + d.Count + 2

 

[Peter_SSs]

but will copy to the bottom twice when I run the macro every time .

What will copy to the bottom twice?

For example, in relation to the original sample data?
Show me the problem please.

 

[abdo meghari]

For example, in relation to the original sample data?
Show me the problem please.

this is what I got when run macro for every time

split brands.xlsm
	A	B	C	D	E	F	G
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	1288	GC 315/80R22.5 AZ124 CHI	20	735.000	14,700.000
15	12	1288	GC 315/80R22.5 AZ124 CHI	20	744.000	14,880.000
16	13	1294	GC 315/80R22.5 AZ188 CHI	20	745.000	14,900.000
17	14	1385	GC 315/80R22.5 AT161 CHI	20	735.000	14,700.000
18			MOVEMENT : SELLING
19	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
20	1	1305	BS 700R16 R230 JAP	2	770.000	1,540.000
21	2	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
22	3	1306	BS 750R16 R230 JAP	4	780.000	3,120.000
23
24				MOVEMENT : PURCHASING
25	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
26	2024.01.02	114.000	1284	GC 1200R20 AZ0026 CHI	80.000	895.000	242,560.00
27			1285	GC 1200R20 AZ0183 CHI	40.000	925.000
28			1385	GC 315/80R22.5 AT161 CHI	20.000	735.000
29			1287	GC 315/80R22.5 AZ126 CHI	20.000	735.000
30			1294	GC 315/80R22.5 AZ188 CHI	20.000	745.000
31			1241	BS 1200R20 G580 JAP	40.000	2,035.000
32			1227	BS 215/70R15C R623 THI	8.000	425.000
33			1221	BS 205/70R15C R623 THI	12.000	405.000
34			1288	GC 315/80R22.5 AZ124 CHI	20.000	735.000
35	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
36	2024.01.09	121	1287	GC 315/80R22.5 AZ126 CHI	14.000	735.000	10,290.00
37			1288	GC 315/80R22.5 AZ124 CHI	20.000	744.000
38	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
39	2024.01.10	125	1227	BS 215/70R15C R623 THI	2.000	425.000	850.00
40	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
41	2024.01.20	148	1287	GC 315/80R22.5 AZ126 CHI	26.000	735.000	19,110.00
42	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
43	2024.01.20	149	1221	BS 205/70R15C R623 THI	50.000	405.000	391,650.00
44			1227	BS 215/70R15C R623 THI	12.000	425.000
45			1241	BS 1200R20 G580 JAP	180.000	2,035.000
46	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
47	2024.03.30	217	1285	GC 1200R20 AZ0183 CHI	10.000	1,225.000	12,250.00
48	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
49	2024.04.09	226	1227	BS 215/70R15C R623 THI	4.000	544.000	2,176.00
50	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
51	2024.04.14	230	1285	GC 1200R20 AZ0183 CHI	40.000	1,205.000	81,950.00
52			1284	GC 1200R20 AZ0026 CHI	30.000	1,125.000
53	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
54	2024.04.17	236	1284	GC 1200R20 AZ0026 CHI	20.000	1,125.000	46,600.00
55			1285	GC 1200R20 AZ0183 CHI	20.000	1,205.000
56				MOVEMENT : SELLING
57	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
58	2024.01.24	561	1306	BS 750R16 R230 JAP	10.000	775.000	7,750.00
59	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
60	2024.01.24	562	1306	BS 750R16 R230 JAP	2.000	780.000	1,560.00
61	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
62	2024.01.24	563	1306	BS 750R16 R230 JAP	2.000	780.000	3,100.00
63			1305	BS 700R16 R230 JAP	2.000	770.000
64	SUMMARY
65	TOTAL PURCHASE	807436
66	TOTAL SELLING	12410
RESULT

Cell Formulas
Range	Formula
F20:F22,F4:F17	F4	=D4*E4

 

[Peter_SSs]

I don't believe that result is possible with the code that I provided and original data in the layout you gave as a sample.
In your original sample data for the 'INVOICES' sheet there is a blank row after the main data and before the 'SUMMARY' sections at the bottom.
Could that blank row have disappeared in your 'INVOICES' worksheet?

 

[abdo meghari]

Could that blank row have disappeared in your 'INVOICES' worksheet?

sorry my bad !
every thing is great !
many thanks for your time & assistance.