Evaluate data for any price changes

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
843
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFGHIJ
5Not really needed if I have column J workingHELP
6IdentifierUnique IdentifierGroupGroup Class Price SKUFileFile NameFrequencyAny Change?
7AAA123456789Team11$ 10.00SKUAAANonFXXXXXXX12
8BBB987654321Team12$ 10.50SKUBBBNonFXXXXXXX11
9CCC112233446Team21$ 75.00SKUCCCNonFXXXXXXX11
10DDD124512566Team22$ 69.80SKUDDDNonFXXXXXXX11
11EEE122456789Team23$ 100.25SKUEEENonFXXXXXXX12
12FFF324512566Team34$ 100.00SKUFFFNonFXXXXXXX12
13GGG924512566Team35$ 74.00SKUGGGNonFXXXXXXX12
14HHH712233446Team498$ 9.00SKUHHHNonFXXXXXXX14
15III522456789Team598$ 5.60SKUIIINonFXXXXXXX13
16LLL624512566Team698$ 7.50SKULLLNonFXXXXXXX13
17AAA123456789Team11$ 9.00SKUAAANonFXXXXXXX12
18EEE122456789Team23$ 5.00SKUEEENonFXXXXXXX12
19FFF324512566Team34$ 7.00SKUFFFNonFXXXXXXX12
20GGG924512566Team35$ 74.00SKUGGGNonFXXXXXXX12
21HHH712233446Team498$ 9.00SKUHHHNonFXXXXXXX14
22III522456789Team598$ 5.60SKUIIINonFXXXXXXX13
23LLL624512566Team698$ 4.00SKULLLNonFXXXXXXX13
24HHH712233446Team498$ 9.00SKUHHHNonFXXXXXXX14
25III522456789Team598$ 5.60SKUIIINonFXXXXXXX13
26LLL624512566Team698$ 7.50SKULLLNonFXXXXXXX13
27HHH712233446Team498$ 10.00SKUHHHNonFXXXXXXX14
28III522456789Team598$ 5.60SKUIIINonFXXXXXXX13
29LLL624512566Team698$ 111.00SKULLLNonFXXXXXXX13
30LLL624512566Team698$ 123.00SKULLLNonFXXXXXXX13
Sheet1
Cell Formulas
RangeFormula
F7:F30F7="SKU"&A7
I7:I30I7=COUNTIF($A$7:$A$27,A7)
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
ABCDEFGHIJKLMN
1Not really needed if I have column J workingHELP
2IdentifierUnique IdentifierGroupGroup Class Price SKUFileFile NameFrequencyAny Change?
3AAA123456789Team1110SKUEEENonFXXXXXXX12yes12
4BBB987654321Team1210.5SKUFFFNonFXXXXXXX12no01
5CCC112233446Team2175SKUGGGNonFXXXXXXX12no01
6DDD124512566Team2269.8SKUHHHNonFXXXXXXX14no01
7EEE122456789Team23100.25SKUIIINonFXXXXXXX14yes12
8FFF324512566Team34100SKULLLNonFXXXXXXX15yes12
9GGG924512566Team3574SKUAAANonFXXXXXXX11no02
10HHH712233446Team4989SKUEEENonFXXXXXXX12yes14
11III522456789Team5985.6SKUFFFNonFXXXXXXX12no04
12LLL624512566Team6987.5SKUGGGNonFXXXXXXX12yes35
13AAA123456789Team119SKUHHHNonFXXXXXXX14yes12
14EEE122456789Team235SKUIIINonFXXXXXXX14yes12
15FFF324512566Team347SKULLLNonFXXXXXXX15yes12
16GGG924512566Team3574SKUHHHNonFXXXXXXX14no02
17HHH712233446Team4989SKUIIINonFXXXXXXX14yes14
18III522456789Team5985.6SKULLLNonFXXXXXXX15no04
19LLL624512566Team6984SKUHHHNonFXXXXXXX14yes45
20HHH712233446Team4989SKUIIINonFXXXXXXX14yes14
21III522456789Team5985.6SKULLLNonFXXXXXXX15no04
22LLL624512566Team6987.5SKULLLNonFXXXXXXX15yes35
23HHH712233446Team49810SKUNonFXXXXXXX10yes34
24III522456789Team5985.6SKUNonFXXXXXXX10no04
25LLL624512566Team698111SKUNonFXXXXXXX10yes45
26LLL624512566Team698123SKUNonFXXXXXXX10yes45
Sheet1
Cell Formulas
RangeFormula
I3:I26I3=COUNTIF($A$7:$A$27,A7)
J3:J26J3=IF(COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)>0, "yes", "no")
F3:F26F3="SKU"&A7
L3:L26L3=COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)
N3:N26N3=COUNTIFS($A$3:$A$26,A3)
 
Last edited:
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWX
1Not really needed if I have column J workingHELP
2IdentifierUnique IdentifierGroupGroup Class Price SKUFileFile NameFrequencyAny Change?
3AAA123456789Team1110SKUEEENonFXXXXXXX12yes12AAA123456789Team1110SKUEEENonFXXXXXXX1
4BBB987654321Team1210.5SKUFFFNonFXXXXXXX12no01AAA123456789Team119SKUHHHNonFXXXXXXX1
5CCC112233446Team2175SKUGGGNonFXXXXXXX12no01EEE122456789Team23100.25SKUIIINonFXXXXXXX1
6DDD124512566Team2269.8SKUHHHNonFXXXXXXX14no01EEE122456789Team235SKUIIINonFXXXXXXX1
7EEE122456789Team23100.25SKUIIINonFXXXXXXX14yes12FFF324512566Team34100SKULLLNonFXXXXXXX1
8FFF324512566Team34100SKULLLNonFXXXXXXX15yes12FFF324512566Team347SKULLLNonFXXXXXXX1
9GGG924512566Team3574SKUAAANonFXXXXXXX11no02HHH712233446Team4989SKUEEENonFXXXXXXX1
10HHH712233446Team4989SKUEEENonFXXXXXXX12yes14HHH712233446Team4989SKUIIINonFXXXXXXX1
11III522456789Team5985.6SKUFFFNonFXXXXXXX12no04HHH712233446Team4989SKUIIINonFXXXXXXX1
12LLL624512566Team6987.5SKUGGGNonFXXXXXXX12yes35HHH712233446Team49810SKUNonFXXXXXXX1
13AAA123456789Team119SKUHHHNonFXXXXXXX14yes12LLL624512566Team6987.5SKUGGGNonFXXXXXXX1
14EEE122456789Team235SKUIIINonFXXXXXXX14yes12LLL624512566Team6984SKUHHHNonFXXXXXXX1
15FFF324512566Team347SKULLLNonFXXXXXXX15yes12LLL624512566Team6987.5SKULLLNonFXXXXXXX1
16GGG924512566Team3574SKUHHHNonFXXXXXXX14no02LLL624512566Team698111SKUNonFXXXXXXX1
17HHH712233446Team4989SKUIIINonFXXXXXXX14yes14LLL624512566Team698123SKUNonFXXXXXXX1
18III522456789Team5985.6SKULLLNonFXXXXXXX15no04
19LLL624512566Team6984SKUHHHNonFXXXXXXX14yes45
20HHH712233446Team4989SKUIIINonFXXXXXXX14yes14
21III522456789Team5985.6SKULLLNonFXXXXXXX15no04
22LLL624512566Team6987.5SKULLLNonFXXXXXXX15yes35
23HHH712233446Team49810SKUNonFXXXXXXX10yes34
24III522456789Team5985.6SKUNonFXXXXXXX10no04
25LLL624512566Team698111SKUNonFXXXXXXX10yes45
26LLL624512566Team698123SKUNonFXXXXXXX10yes45
Sheet1
Cell Formulas
RangeFormula
I3:I26I3=COUNTIF($A$7:$A$27,A7)
J3:J26J3=IF(COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)>0, "yes", "no")
Q3:X17Q3=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:F26F3="SKU"&A7
L3:L26L3=COUNTIFS($A$3:$A$26,A3,$E$3:$E$26,"<>"&E3)
N3:N26N3=COUNTIFS($A$3:$A$26,A3)
Dynamic array formulas.
 
Upvote 0
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

VBA Code:
.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))"
 
Upvote 0
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
 
Upvote 0
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?

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

VBA Code:
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
 
Upvote 0
sorry i dont provide VBA solutions on the forums
i didn't realise you wanted a VBA solution
 
Upvote 0
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
 
Upvote 0
-Revising the code-
 
Last edited:
Upvote 0
This assumes your headers are in row 1 and your data starts in row 2. Try on a copy.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top