Getting the last price for each duplicates ID

[Hasson]

Hi,
I would show report in OUTPUT sheet by brings batches from column C and brings the last price for each duplicates batch from column E,F for each sheet.

Microsoft‫‬.xlsx
	A	B	C	D	E	F
1	ITEM	DATE	BATCH	CUS	PRICE	PRICE1
2	1	01/01/2022	BTS00	CVF-00	111.00	155.00
3	2	01/01/2022	BTS01	CVF-01	110.00	115.00
4	3	01/01/2022	BTS02	CVF-02	221.00	250.00
5	4	01/01/2022	BTS03	CVF-03		155.00
6	5	01/01/2022	BTS01	CVF-04	120.00	140.00
7	6	01/01/2022	BTS02	CVF-05	115.00	120.00
8	7	01/01/2022	BTS00	CVF-06	114.00	120.00
9	8	03/01/2023	BTS01	CVF-07	122.00	144.00
10	9	04/01/2023	BTS02	CVF-08	120.00	177.00
11	10	05/01/2023	BTS00	CVF-09	120.00	160.00
12	11	03/02/2023	BTS01	CVF-10	108.00	139.00
13	12	04/02/2023	BTS02	CVF-11	120.00	177.00
14	13	05/02/2023	BTS00	CVF-12	115.00	122.00
15	14	06/02/2023	BTS04	CVF-13	100.00	110.00
16	15	07/02/2023	BTS05	CVF-14	120.00	125.00
17	16	08/02/2023	BTS06	CVF-15	115.00	120.00
PRICES

Microsoft.xlsx
	A	B	C	D	E	F
1	ITEM	DATE	BATCH	CNN	SSL	SSL1
2	1	01/01/2022	BTS02	CVF-000	150.00	130.00
3	2	01/01/2022	BTS00	CVF-001	170.00	200.00
4	3	03/01/2023	BTS01	CVF-002	155.00	160.00
5	4	01/01/2022	BTS03	CVF-003	170.00	220.00
6	5	01/01/2022	BTS00	CVF-004	160.00	180.00
7	6	01/01/2022	BTS01	CVF-005	140.00	150.00
8	7	01/01/2022	BTS02	CVF-006	221.00	250.00
9	8	01/01/2022	BTS03	CVF-007	166.00	170.00
10	9	01/01/2022	BTS00	CVF-008	140.00	145.00
11	10	01/01/2022	BTS01	CVF-009	130.00	144.00
12	11	01/01/2022	BTS02	CVF-010	221.00	250.00
13	12	07/02/2023	BTS05	CVF-011	125.00
14	13	08/02/2023	BTS06	CVF-012	120.00	130.00
15	14	08/02/2023	BTS06	CVF-013	122.00	123.00
SSL

and report should be

Microsoft‫‬.xlsx
	A	B	C	D	E	F
1	ITEM	BATCH	PRICE	PRICE1	SSL	SSL1
2	1	BTS00	115.00	122.00	140.00	145.00
3	2	BTS01	108.00	139.00	130.00	144.00
4	3	BTS02	120.00	177.00	221.00	250.00
5	4	BTS03		155.00	166.00	170.00
6	5	BTS04	100.00	110.00
7	6	BTS05	120.00	125.00	125.00
8	7	BTS06	115.00	120.00	122.00	123.00
OUTPUT

should replace data when every time running the macro.
I have about 1500 batches and 8000 rows when repeat data for batches
thanks

 

[bebo021999]

Use this code

Option Explicit
Sub TEST()
Dim i&, j&, k&, rng, res(1 To 10000, 1 To 6), c As Boolean
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
With Sheets("PRICES")
    rng = .Range("A1").CurrentRegion.Value ' assume A1 have data
    For i = UBound(rng) To 2 Step -1 'assume date is in creasing. loop from bottom and get the 1st unique batch found
        If Not dic.exists(rng(i, 3)) Then ' if 1st found then store in an array
            dic.Add rng(i, 3), ""
            k = k + 1: res(k, 1) = k: res(k, 2) = rng(i, 3)
            res(k, 3) = rng(i, 5): res(k, 4) = rng(i, 6)
        End If
    Next
End With
dic.RemoveAll
With Sheets("SSL")
    rng = .Range("A1").CurrentRegion.Value
    For i = UBound(rng) To 2 Step -1
        If Not dic.exists(rng(i, 3)) Then
            dic.Add rng(i, 3), "": c = False
            For j = 1 To k
                If res(j, 2) = rng(i, 3) Then
                    c = True: res(j, 5) = rng(i, 5): res(j, 6) = rng(i, 6)
                End If
            Next
            If Not c Then
                k = k + 1: res(k, 1) = k: res(k, 2) = rng(i, 3)
                res(k, 5) = rng(i, 5): res(k, 6) = rng(i, 6)
            End If
        End If
    Next
End With
Sheets("OUTPUT").Activate
Set dic = Nothing
If k = 0 Then Exit Sub
Range("A2:F10000").ClearContents
Range("A2").Resize(k, 6).Value = res
Range("B2:F" & k + 1).Sort key1:=Range("B1")
End Sub

 

[Hasson]

Awesome !
this is exactly what I want it.
thank you.