Macro to delete duplicates ID contains same price for 1000 elements

[Omar M]

Hi,
I don't want POWERQUERY or PIVOT TABLE , just CODE.
I would search for ID in column B contains same price in column E then should delete them except duplicates ID contains different prices.
I have about 1000 elements in column B after repeat elements many times the data could be 12000 rows. so I put the result in range (J:N) , I don't need NAME column.

a
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	ID	NAME	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	OMAR	22.00	222.00	4,884.00
3	02/03/2024	AZSDC	OMRAN	10.00	120.00	1,200.00
4	03/03/2024	ASCV TYU T566	AMERN	10.00	100.00	1,000.00
5	04/03/2024	AVSD HJY00	AMUR	5.00	222.00	1,110.00
6	05/03/2024	AZSDC	ASIF	5.00	123.00	615.00
7	06/03/2024	ASCV TYU T566	SAAF	5.00	110.00	550.00
8	07/03/2024	BBSDFRT-00	SAAF	1.00	120.00	120.00
9	08/03/2024	BBSDFRT-01	ASIF	2.00	124.00	248.00
10	09/03/2024	BBSDFRT-00	ASIF	3.00	120.00	360.00
11	10/03/2024	VBGHYT 34YTY 66	SUMMER	2.00	220.00	440.00
12	11/03/2024	ASCV TYU T566	SUMMER	1.00	110.00	110.00
13	12/03/2024	VBGHYT 34YTY 66	ASIF	2.00	225.00	450.00
SS

Cell Formulas
Range	Formula
F2:F13	F2	=D2*E2

what I want

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	ID	NAME	QTY	UNIT PRICE	TOTAL				DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	OMAR	22.00	222.00	4,884.00				01/03/2024	AVSD HJY00	22.00	222.00	4,884.00
3	02/03/2024	AZSDC	OMRAN	10.00	120.00	1,200.00				02/03/2024	AZSDC	10.00	120.00	1,200.00
4	03/03/2024	ASCV TYU T566	AMERN	10.00	100.00	1,000.00				03/03/2024	ASCV TYU T566	10.00	100.00	1,000.00
5	04/03/2024	AVSD HJY00	AMUR	5.00	222.00	1,110.00				05/03/2024	AZSDC	5.00	123.00	615.00
6	05/03/2024	AZSDC	ASIF	5.00	123.00	615.00				06/03/2024	ASCV TYU T566	5.00	110.00	550.00
7	06/03/2024	ASCV TYU T566	SAAF	5.00	110.00	550.00				07/03/2024	BBSDFRT-00	1.00	120.00	120.00
8	07/03/2024	BBSDFRT-00	SAAF	1.00	120.00	120.00				08/03/2024	BBSDFRT-01	2.00	124.00	248.00
9	08/03/2024	BBSDFRT-01	ASIF	2.00	124.00	248.00				10/03/2024	VBGHYT 34YTY 66	2.00	220.00	440.00
10	09/03/2024	BBSDFRT-00	ASIF	3.00	120.00	360.00				12/03/2024	VBGHYT 34YTY 66	2.00	225.00	450.00
11	10/03/2024	VBGHYT 34YTY 66	SUMMER	2.00	220.00	440.00
12	11/03/2024	ASCV TYU T566	SUMMER	1.00	110.00	110.00
13	12/03/2024	VBGHYT 34YTY 66	ASIF	2.00	225.00	450.00
SS

Cell Formulas
Range	Formula
F2:F13,N2:N10	N2	=L2*M2

should delete data in range J:N when run macro every time .
Thanks

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub Test()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Columns("J:N").ClearContents
  With Range("J1:N" & lr)
    .Value = Application.Index(Cells, Evaluate("row(1:" & lr & ")"), Array(1, 2, 4, 5, 6))
    .RemoveDuplicates Columns:=Array(2, 4), Header:=xlYes
  End With
End Sub

 

[Omar M]

impressive solution.
short ,efficient code.
many thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.