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)

 

[ItalianPlatinum]

So I got spoiled by Etafs formulas because it also kicked back the price changes which I will eventually need. I for some reason cannot get yours to run my data starts on row 7 so i modified as so on my copy. thoughts? is there a way to capture that component too? There is no way to play his formula through VBA in anyway?

it got stuck at this:

If id(i, 1) = id(j, 1) And price(i, 1) <> price(j, 1) Then

Sub PriceChanges()
    Dim id, price, output()
    Dim i As Long, j As Long, numRow As Long
    Dim ws As Worksheet
    Dim checkedIDs As Object
    Set checkedIDs = CreateObject("Scripting.Dictionary")
   
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    numRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    id = ws.Range("A7:A" & numRow).Value2
    price = ws.Range("E7:E" & numRow).Value2
    ReDim output(1 To numRow, 1 To 1)
   
    For i = 1 To numRow - 1
        If Not checkedIDs.exists(id(i, 1)) Then
            checkedIDs.Add id(i, 1), ""
            For j = i + 1 To numRow - 1
                If id(i, 1) = id(j, 1) And price(i, 1) <> price(j, 1) Then
                    checkedIDs(id(i, 1)) = "yes"
                    output(i, 1) = checkedIDs(id(i, 1))
                    Exit For
                Else
                    checkedIDs(id(i, 1)) = "no"
                    output(i, 1) = checkedIDs(id(i, 1))
                End If
            Next j
        Else
            output(i, 1) = checkedIDs(id(i, 1))
        End If
    Next i
   
    ws.Range("J7").Resize(UBound(output, 1), 1).Value = output
End Sub

 

[Cubist]

How about this? Adjusted for data in row 7 and returned the new price for the first instances.

Sub PriceChanges2()
    Dim id, price, output()
    Dim i As Long, j As Long, numRow As Long
    Dim ws As Worksheet
    Dim checkedIDs As Object
    Set checkedIDs = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    numRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    id = ws.Range("A7:A" & numRow).Value2
    price = ws.Range("E7:E" & numRow).Value2
    ReDim output(1 To numRow, 1 To 2)
    For i = 1 To numRow - 6 'data row minus 1
        If Not checkedIDs.exists(id(i, 1)) Then
            checkedIDs.Add id(i, 1), price(i, 1)
            For j = i + 1 To numRow - 6 'data row minus 1
                If id(i, 1) = id(j, 1) And price(i, 1) <> price(j, 1) Then
                    checkedIDs(id(i, 1)) = "yes"
                    checkedIDs(price(i, 1)) = price(j, 1)
                    output(i, 1) = checkedIDs(id(i, 1))
                    output(i, 2) = checkedIDs(price(i, 1))
                    Exit For
                Else
                    checkedIDs(id(i, 1)) = "no"
                    checkedIDs(price(i, 1)) = price(j, 1)
                    output(i, 1) = checkedIDs(id(i, 1))
                    output(i, 2) = price(i, 1)
                End If
            Next j
        Else
            output(i, 1) = checkedIDs(id(i, 1))
            output(i, 2) = ""
        End If
    Next i
    ws.Range("J7").Resize(UBound(output, 1), 2).Value = output
End Sub

 

[ItalianPlatinum]

Ok that works at least but the data is considerably different than the formula route. is there no way me for to just set that sort, unque filter formula in a range? I even tried Forumula2 and that didnt work

 

[Cubist]

Not getting what you’re trying to do. If you want a formula why use a macro to insert a formula? Why not just use the formula?

 

[ItalianPlatinum]

I have users running the VBA daily so I cannot "leave" a formula in that cell. The VBA kicks off with deleting all cells in each sheet then running through its process. So is there no way just to "place" a formula in the range as if I am doing myself? I am sure I am over simplifying something that is more complex than that. I am sure replicating it via VBA is cumbersome.

 

[Alex Blakenburg]

Ok having slight problem getting it set with VBA when I do the below which i think should work it says "TRUE" regardless of changes or not. Do you see anything visibly wrong I am doing?

This was not the original question but just in terms of converting @etaf formula to VBA you can give this a try.
PS: Normally people just use a single "@" I just wanted it to stand out. It is a place holder that the replace uses to insert the last row value.
I expect it will only work in MS365 or O 2021 though

Sub AddFormula()

Dim WsSummary As Worksheet, WsChanges As Worksheet
Dim lrS As Long
Dim strForm As String

Set WsSummary = Worksheets("Summary")
Set WsChanges = Worksheets("Changes")

With WsSummary
    lrS = .Cells(Rows.Count, "E").End(xlUp).Row
End With

With WsChanges
    .Cells.ClearContents
    strForm = "=SORT(UNIQUE(FILTER(Summary!$A$7:$H$@@@,COUNTIFS(Summary!$A$7:$A$@@@,Summary!$A$7:$A$@@@,Summary!$E$7:$E$@@@,""<>""&Summary!$E$7:$E$@@@)>0,""No Changes""),1))"
    .Range("A2").Formula2 = Replace(strForm, "@@@", lrS)

End With

End Sub

 

[ItalianPlatinum]

Nice, wonderful that worked. Thanks for all of you for helping me on this. Think I am all set here. Not too sure the concept of @@@ but it sure worked lovely

 

[Cubist]

If you're working with a large data set consider using VBA arrays to optimize performance.
What do you consider as duplicate record? Do you only look at the Identifier and Price ... Or do you consider other columns as well?

 

[Cubist]

This VBA formula simulates the FILTER, SORT and UNIQUE using VBA arrays. It also only considers Identifier and Price for duplicate records (unless you indicate otherwise). See the comparison to the provided formula by@etaf. There should be a significant difference in performance compared to using the formula, however, it's harder to maintain. See which option is better for you.

Book1
	Q	R	S	T	U	V	W	X	Y											AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
2	etaf	etaf	etaf	etaf	etaf	etaf	etaf	etaf												Cubist	Cubist	Cubist	Cubist	Cubist	Cubist	Cubist	Cubist	Cubist	Cubist
3	AAA	123456789	Team1	1	10	SKUEEE	NonF	XXXXXXX1												AAA	1.23E+08	Team1	1	10	SKUEEE	NonF	XXXXXXX1	2	Yes
4	AAA	123456789	Team1	1	9	SKUHHH	NonF	XXXXXXX1												AAA	1.23E+08	Team1	1	9	SKUHHH	NonF	XXXXXXX1	4	Yes
5	EEE	122456789	Team2	3	100.25	SKUIII	NonF	XXXXXXX1												EEE	1.22E+08	Team2	3	100.25	SKUIII	NonF	XXXXXXX1	4	Yes
6	EEE	122456789	Team2	3	5	SKUIII	NonF	XXXXXXX1												EEE	1.22E+08	Team2	3	5	SKUIII	NonF	XXXXXXX1	4	Yes
7	FFF	324512566	Team3	4	100	SKULLL	NonF	XXXXXXX1												FFF	3.25E+08	Team3	4	100	SKULLL	NonF	XXXXXXX1	5	Yes
8	FFF	324512566	Team3	4	7	SKULLL	NonF	XXXXXXX1												FFF	3.25E+08	Team3	4	7	SKULLL	NonF	XXXXXXX1	5	Yes
9	HHH	712233446	Team4	98	9	SKUEEE	NonF	XXXXXXX1												HHH	7.12E+08	Team4	98	10	SKU	NonF	XXXXXXX1	0	Yes
10	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1												LLL	6.25E+08	Team6	98	4	SKUHHH	NonF	XXXXXXX1	4	Yes
11	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1												LLL	6.25E+08	Team6	98	111	SKU	NonF	XXXXXXX1	0	Yes
12	HHH	712233446	Team4	98	10	SKU	NonF	XXXXXXX1												LLL	6.25E+08	Team6	98	123	SKU	NonF	XXXXXXX1	0	Yes
13	LLL	624512566	Team6	98	7.5	SKUGGG	NonF	XXXXXXX1
14	LLL	624512566	Team6	98	4	SKUHHH	NonF	XXXXXXX1
15	LLL	624512566	Team6	98	7.5	SKULLL	NonF	XXXXXXX1
16	LLL	624512566	Team6	98	111	SKU	NonF	XXXXXXX1
17	LLL	624512566	Team6	98	123	SKU	NonF	XXXXXXX1
Sheet4

Cell Formulas
Range	Formula
Q3:X17	Q3	=SORT(UNIQUE(FILTER(A3:H26,COUNTIFS($A$3:$A$26,$A$3:$A$26,$E$3:$E$26,"<>"&$E$3:$E$26)>0),1))
AJ3:AS12	AJ3	=SORT(Z3:AI12)
Dynamic array formulas.

Sub PriceChanges4()
    Dim id, price, output()
    Dim i As Long, numRow As Long
    Dim ws As Worksheet
    Dim uniquePrices As Object
    Set uniquePrices = 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)
    For i = 2 To numRow - 5
        If Not uniquePrices.exists(id(i, 1)) Then
            uniquePrices.Add id(i, 1), CreateObject("Scripting.Dictionary")
        End If
        If Not uniquePrices(id(i, 1)).exists(price(i, 1)) Then
            uniquePrices(id(i, 1)).Add price(i, 1), "Yes"
        Else
            uniquePrices(id(i, 1))(price(i, 1)) = "No"
        End If
    Next i
    ' Check if there's only one price for each ID and mark as "No"
    For i = 2 To numRow - 5
        If uniquePrices(id(i, 1)).Count = 1 Then
            uniquePrices(id(i, 1))(price(i, 1)) = "No"
        End If
    Next i
 
    For i = 2 To numRow - 5
        output(i - 1, 1) = uniquePrices(id(i, 1))(price(i, 1))
    Next i
    ' Output the result to the worksheet
    ws.Range("J7").Resize(UBound(output, 1), 1).Value = output
 
    With Range("A6:J" & Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=10, Criteria1:="Yes" 'Field 10 = Column J
        .Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlYes
        .Copy Range("Q6")
        .AutoFilter
    End With
 
End Sub

 

[ItalianPlatinum]

If you're working with a large data set consider using VBA arrays to optimize performance.
What do you consider as duplicate record? Do you only look at the Identifier and Price ... Or do you consider other columns as well?

yea just identifier and price. I will give your code a try. Because I am working with a lot of data so there could be some performance degradation - i have yet to try either on the big data set.