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)

 

[etaf]

how about
=COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)

and use in an IF
=IF(COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)>0, "yes", "no")

What version of excel will this be used with - you have lots in your profile - probably better to just have the version you want to use or be used with - as lots of different functions now

You could probably use filter to pull out the list if you needed to - as you have 2021 version which has FILTER() function, also UNIQUE() to pull out just the unique entries into a list filtered

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1									Not really needed if I have column J working	HELP
2	Identifier	Unique Identifier	Group	Group Class	Price	SKU	File	File Name	Frequency	Any Change?
3	AAA	123456789	Team1	1	10	SKUEEE	NonF	XXXXXXX1	2	yes		1		2
4	BBB	987654321	Team1	2	10.5	SKUFFF	NonF	XXXXXXX1	2	no		0		1
5	CCC	112233446	Team2	1	75	SKUGGG	NonF	XXXXXXX1	2	no		0		1
6	DDD	124512566	Team2	2	69.8	SKUHHH	NonF	XXXXXXX1	4	no		0		1
7	EEE	122456789	Team2	3	100.25	SKUIII	NonF	XXXXXXX1	4	yes		1		2
8	FFF	324512566	Team3	4	100	SKULLL	NonF	XXXXXXX1	5	yes		1		2
9	GGG	924512566	Team3	5	74	SKUAAA	NonF	XXXXXXX1	1	no		0		2
10	HHH	712233446	Team4	98	9	SKUEEE	NonF	XXXXXXX1	2	yes		1		4
11	III	522456789	Team5	98	5.6	SKUFFF	NonF	XXXXXXX1	2	no		0		4
12	LLL	624512566	Team6	98	7.5	SKUGGG	NonF	XXXXXXX1	2	yes		3		5
13	AAA	123456789	Team1	1	9	SKUHHH	NonF	XXXXXXX1	4	yes		1		2
14	EEE	122456789	Team2	3	5	SKUIII	NonF	XXXXXXX1	4	yes		1		2
15	FFF	324512566	Team3	4	7	SKULLL	NonF	XXXXXXX1	5	yes		1		2
16	GGG	924512566	Team3	5	74	SKUHHH	NonF	XXXXXXX1	4	no		0		2
17	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1	4	yes		1		4
18	III	522456789	Team5	98	5.6	SKULLL	NonF	XXXXXXX1	5	no		0		4
19	LLL	624512566	Team6	98	4	SKUHHH	NonF	XXXXXXX1	4	yes		4		5
20	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1	4	yes		1		4
21	III	522456789	Team5	98	5.6	SKULLL	NonF	XXXXXXX1	5	no		0		4
22	LLL	624512566	Team6	98	7.5	SKULLL	NonF	XXXXXXX1	5	yes		3		5
23	HHH	712233446	Team4	98	10	SKU	NonF	XXXXXXX1	0	yes		3		4
24	III	522456789	Team5	98	5.6	SKU	NonF	XXXXXXX1	0	no		0		4
25	LLL	624512566	Team6	98	111	SKU	NonF	XXXXXXX1	0	yes		4		5
26	LLL	624512566	Team6	98	123	SKU	NonF	XXXXXXX1	0	yes		4		5
Sheet1

Cell Formulas
Range	Formula
I3:I26	I3	=COUNTIF($A$7:$A$27,A7)
J3:J26	J3	=IF(COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)>0, "yes", "no")
F3:F26	F3	="SKU"&A7
L3:L26	L3	=COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)
N3:N26	N3	=COUNTIFS($A$3:$A$26,A3)

 

[etaf]

just in case you wanted to use FILTER() , UNIQUE() and SORT()

=SORT(UNIQUE(FILTER(A3:H26,COUNTIFS($A$3:$A$26,$A$3:$A$26,$E$3:$E$26,"<>"&$E$3:$E$26)>0),1))

see results in column Q

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1									Not really needed if I have column J working	HELP
2	Identifier	Unique Identifier	Group	Group Class	Price	SKU	File	File Name	Frequency	Any Change?
3	AAA	123456789	Team1	1	10	SKUEEE	NonF	XXXXXXX1	2	yes		1		2			AAA	123456789	Team1	1	10	SKUEEE	NonF	XXXXXXX1
4	BBB	987654321	Team1	2	10.5	SKUFFF	NonF	XXXXXXX1	2	no		0		1			AAA	123456789	Team1	1	9	SKUHHH	NonF	XXXXXXX1
5	CCC	112233446	Team2	1	75	SKUGGG	NonF	XXXXXXX1	2	no		0		1			EEE	122456789	Team2	3	100.25	SKUIII	NonF	XXXXXXX1
6	DDD	124512566	Team2	2	69.8	SKUHHH	NonF	XXXXXXX1	4	no		0		1			EEE	122456789	Team2	3	5	SKUIII	NonF	XXXXXXX1
7	EEE	122456789	Team2	3	100.25	SKUIII	NonF	XXXXXXX1	4	yes		1		2			FFF	324512566	Team3	4	100	SKULLL	NonF	XXXXXXX1
8	FFF	324512566	Team3	4	100	SKULLL	NonF	XXXXXXX1	5	yes		1		2			FFF	324512566	Team3	4	7	SKULLL	NonF	XXXXXXX1
9	GGG	924512566	Team3	5	74	SKUAAA	NonF	XXXXXXX1	1	no		0		2			HHH	712233446	Team4	98	9	SKUEEE	NonF	XXXXXXX1
10	HHH	712233446	Team4	98	9	SKUEEE	NonF	XXXXXXX1	2	yes		1		4			HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1
11	III	522456789	Team5	98	5.6	SKUFFF	NonF	XXXXXXX1	2	no		0		4			HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1
12	LLL	624512566	Team6	98	7.5	SKUGGG	NonF	XXXXXXX1	2	yes		3		5			HHH	712233446	Team4	98	10	SKU	NonF	XXXXXXX1
13	AAA	123456789	Team1	1	9	SKUHHH	NonF	XXXXXXX1	4	yes		1		2			LLL	624512566	Team6	98	7.5	SKUGGG	NonF	XXXXXXX1
14	EEE	122456789	Team2	3	5	SKUIII	NonF	XXXXXXX1	4	yes		1		2			LLL	624512566	Team6	98	4	SKUHHH	NonF	XXXXXXX1
15	FFF	324512566	Team3	4	7	SKULLL	NonF	XXXXXXX1	5	yes		1		2			LLL	624512566	Team6	98	7.5	SKULLL	NonF	XXXXXXX1
16	GGG	924512566	Team3	5	74	SKUHHH	NonF	XXXXXXX1	4	no		0		2			LLL	624512566	Team6	98	111	SKU	NonF	XXXXXXX1
17	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1	4	yes		1		4			LLL	624512566	Team6	98	123	SKU	NonF	XXXXXXX1
18	III	522456789	Team5	98	5.6	SKULLL	NonF	XXXXXXX1	5	no		0		4
19	LLL	624512566	Team6	98	4	SKUHHH	NonF	XXXXXXX1	4	yes		4		5
20	HHH	712233446	Team4	98	9	SKUIII	NonF	XXXXXXX1	4	yes		1		4
21	III	522456789	Team5	98	5.6	SKULLL	NonF	XXXXXXX1	5	no		0		4
22	LLL	624512566	Team6	98	7.5	SKULLL	NonF	XXXXXXX1	5	yes		3		5
23	HHH	712233446	Team4	98	10	SKU	NonF	XXXXXXX1	0	yes		3		4
24	III	522456789	Team5	98	5.6	SKU	NonF	XXXXXXX1	0	no		0		4
25	LLL	624512566	Team6	98	111	SKU	NonF	XXXXXXX1	0	yes		4		5
26	LLL	624512566	Team6	98	123	SKU	NonF	XXXXXXX1	0	yes		4		5
Sheet1

Cell Formulas
Range	Formula
I3:I26	I3	=COUNTIF($A$7:$A$27,A7)
J3:J26	J3	=IF(COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)>0, "yes", "no")
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))
F3:F26	F3	="SKU"&A7
L3:L26	L3	=COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)
N3:N26	N3	=COUNTIFS($A$3:$A$26,A3)
Dynamic array formulas.

 

[ItalianPlatinum]

So I use Office 365 at work but then at home I use different ones. But point taken 90% is needed for my work. Ill amend. I like the sort, unique, filter that is clever because then I can toss it on a new tab called "changes." The only dilemma with that is what happens if there is no changes? Like can I VBA code it with a message to skip if no changes? I noticed if no changes it does #CALC! also setting it with VBA is there a special way to do it I have it like this but not quite working

.Range("A7").Formula = "=SORT(UNIQUE(FILTER(A7:H30,COUNTIFS($A$7:$A" & lrS & ",$A$7:$A" & lrS & ",$E$7:$E" & lrS & "," <> "&$E$7:$E" & lrS & ")>0),1))"

 

[etaf]

there is an option on filter() for if empty
so you can use
=SORT(UNIQUE(FILTER(A3:H26,COUNTIFS($A$3:$A$26,$A$3:$A$26,$E$3:$E$26,"<>"&$E$3:$E$26)>0,"No Changes"),1))

or whatever message

 

[ItalianPlatinum]

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?

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

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

 

[etaf]

sorry i dont provide VBA solutions on the forums
i didn't realise you wanted a VBA solution

 

[ItalianPlatinum]

I don’t so much even need a VBA solution just need to apply the formula with VBA and when doing so it’s not calculating correctly - I guess I’ll wait until someone may have an idea on it

 

[Cubist]

-Revising the code-

 

[Cubist]

This assumes your headers are in row 1 and your data starts in row 2. Try on a copy.

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("A2:A" & numRow).Value2
    price = ws.Range("E2: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("J2").Resize(UBound(output, 1), 1).Value = output
End Sub