Evaluate data for any price changes

[ItalianPlatinum]

I am trying to find a way to easily know if any price changes from a very large dataset. Over 100k rows. Currently I am filtering by frequency, then using pivot tables and its cumbersome. I am looking if I can just have a column called "changes" so I only have to focus on any duplicates that have changes. There could be a lot of duplicates with the same price but I don't really care about those. The unique Identifier column A is what sets them apart. I did a snipbit below using XLBB to give a frame of reference what I am dealing with. In the future I will probably looking for a way to give me the changes but for right now babysteps and just looking for changes "Yes or No". Thank you as always for helping look at this.

Book1
	A	B	C	D	E	F	G	H	I	J
5									Not really needed if I have column J working	HELP
6	Identifier	Unique Identifier	Group	Group Class	Price	SKU	File	File Name	Frequency	Any Change?
7	AAA	123456789	Team1	1	$ 10.00	SKUAAA	NonF	XXXXXXX1	2
8	BBB	987654321	Team1	2	$ 10.50	SKUBBB	NonF	XXXXXXX1	1
9	CCC	112233446	Team2	1	$ 75.00	SKUCCC	NonF	XXXXXXX1	1
10	DDD	124512566	Team2	2	$ 69.80	SKUDDD	NonF	XXXXXXX1	1
11	EEE	122456789	Team2	3	$ 100.25	SKUEEE	NonF	XXXXXXX1	2
12	FFF	324512566	Team3	4	$ 100.00	SKUFFF	NonF	XXXXXXX1	2
13	GGG	924512566	Team3	5	$ 74.00	SKUGGG	NonF	XXXXXXX1	2
14	HHH	712233446	Team4	98	$ 9.00	SKUHHH	NonF	XXXXXXX1	4
15	III	522456789	Team5	98	$ 5.60	SKUIII	NonF	XXXXXXX1	3
16	LLL	624512566	Team6	98	$ 7.50	SKULLL	NonF	XXXXXXX1	3
17	AAA	123456789	Team1	1	$ 9.00	SKUAAA	NonF	XXXXXXX1	2
18	EEE	122456789	Team2	3	$ 5.00	SKUEEE	NonF	XXXXXXX1	2
19	FFF	324512566	Team3	4	$ 7.00	SKUFFF	NonF	XXXXXXX1	2
20	GGG	924512566	Team3	5	$ 74.00	SKUGGG	NonF	XXXXXXX1	2
21	HHH	712233446	Team4	98	$ 9.00	SKUHHH	NonF	XXXXXXX1	4
22	III	522456789	Team5	98	$ 5.60	SKUIII	NonF	XXXXXXX1	3
23	LLL	624512566	Team6	98	$ 4.00	SKULLL	NonF	XXXXXXX1	3
24	HHH	712233446	Team4	98	$ 9.00	SKUHHH	NonF	XXXXXXX1	4
25	III	522456789	Team5	98	$ 5.60	SKUIII	NonF	XXXXXXX1	3
26	LLL	624512566	Team6	98	$ 7.50	SKULLL	NonF	XXXXXXX1	3
27	HHH	712233446	Team4	98	$ 10.00	SKUHHH	NonF	XXXXXXX1	4
28	III	522456789	Team5	98	$ 5.60	SKUIII	NonF	XXXXXXX1	3
29	LLL	624512566	Team6	98	$ 111.00	SKULLL	NonF	XXXXXXX1	3
30	LLL	624512566	Team6	98	$ 123.00	SKULLL	NonF	XXXXXXX1	3
Sheet1

Cell Formulas
Range	Formula
F7:F30	F7	="SKU"&A7
I7:I30	I7	=COUNTIF($A$7:$A$27,A7)

 

[Cubist]

I've optimized the code further; less looping. I've tested on 100K rows. It takes me an average of 1.5 seconds per run.

Sub PriceChanges5()
    Dim id, price, output
    Dim i As Long, numRow As Long
    Dim ws As Worksheet
    Dim uniquePrices As Object, idPrices As Object, firstInstance As Object
    Set uniquePrices = CreateObject("Scripting.Dictionary")
    Set idPrices = CreateObject("Scripting.Dictionary")
    Set firstInstance = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    numRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    id = ws.Range("A6:A" & numRow).Value2
    price = ws.Range("E6:E" & numRow).Value2
    ReDim output(1 To numRow, 1 To 1)
    Dim t As Double: t = Timer
   
    ' Loop through the data to populate the dictionaries
    For i = 1 To numRow - 5
        If Not idPrices.exists(id(i, 1)) Then
            idPrices.Add id(i, 1), CreateObject("Scripting.Dictionary")
        End If
        If Not idPrices(id(i, 1)).exists(price(i, 1)) Then
            idPrices(id(i, 1)).Add price(i, 1), True
        End If
    Next i
    ' Loop through the data again to populate the output array
    For i = 2 To numRow - 5
        If idPrices(id(i, 1)).Count = 1 Then
            output(i - 1, 1) = "No" ' Only one price for this ID
        ElseIf idPrices(id(i, 1)).Count > 1 Then
            ' Multiple distinct prices for this ID
            If Not firstInstance.exists(id(i, 1) & price(i, 1)) Then
                output(i - 1, 1) = "Yes" ' Mark the first instance as "Yes"
                firstInstance.Add id(i, 1) & price(i, 1), True
            Else
                output(i - 1, 1) = "No" ' Mark subsequent instances as "No"
            End If
        End If
    Next i
    ' Output the result to the worksheet
    ws.Range("J7").Resize(UBound(output, 1), 1).Value = output
    ' Apply filter and sort
    With ws.Range("A6:J" & numRow)
        .AutoFilter Field:=10, Criteria1:="Yes" 'Field 10 = Column J
        .Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlYes
        .SpecialCells(xlCellTypeVisible).Copy ws.Range("Q6")
        .AutoFilter
    End With
    MsgBox Timer - t 'Check how long the code runs.
End Sub

 

[ItalianPlatinum]

I plan to test this today. before i start if I wanted to change the output say to a tab called "changes" would the below modification look ok to you? I didn't want to break your code even before trying for the first time on the big data set

Set ws1 = ThisWorkbook.Worksheets("Changes")

    ' Output the result to the worksheet
    ws1.Range("A2").Resize(UBound(output, 1), 1).Value = output
    ' Apply filter and sort
    With ws1.Range("A1:J" & numRow)
        .AutoFilter Field:=10, Criteria1:="Yes" 'Field 10 = Column J
        .Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlYes
        .SpecialCells(xlCellTypeVisible).Copy ws.Range("Q6")
        .AutoFilter
    End With

 

[Cubist]

1) You should test on a COPY, not the original.
2) To change the location output see the red below. Everything else is for Sheet1", the source data, so keep them as is.

    ' Output the result to the worksheet

    ws.Range("J7").Resize(UBound(output, 1), 1).Value = output

    ' Apply filter and sort

    With ws.Range("A6:J" & numRow)

        .AutoFilter Field:=10, Criteria1:="Yes" 'Field 10 = Column J

        .Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlYes

        .SpecialCells(xlCellTypeVisible).Copy ws1.Range("A1")

        .AutoFilter

    End With

 

[ItalianPlatinum]

Okay thanks - i think I got it going with my small set. Even my small set came at 5 seconds. I don't think my large data set should be that bad though to notice. Will look at that later today the file comes nightly at 6PM EST

@Cubist you are raising another issue of mine I posted previously where I use similar formulas (sort, unique, filter) but I have a crazy amount of performance issues (1 time it took 1 day to run), because I have a lot of data. Your expertise may be helpful there Formula to match criteria but then ended up stuck here so I wasn't able to bring that tool forward Find last row of sheet based off cell value to reference the sheet name