Formula to compare latest result vs the previous results. If difference state changed

K

Kingkoopa

Board Regular
Joined
Aug 7, 2014
Messages
94
Hi Guys,

I am using windows 10 and Excel 2010.
[TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl65, width: 113, bgcolor: transparent"]CUSTOMER
[/TD]
[TD="class: xl65, width: 69, bgcolor: transparent"]COUNTRY
[/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"]ITEM CODE
[/TD]
[TD="class: xl65, width: 165, bgcolor: transparent"]START
[/TD]
[TD="class: xl65, width: 173, bgcolor: transparent"]EXPIRE
[/TD]
[TD="class: xl65, width: 71, bgcolor: transparent"]PRICE
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]P01
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]26-Jan-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]10-Jan-18
[/TD]
[TD="class: xl67, bgcolor: transparent"]75.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]P01
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]11-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]11-Jan-19
[/TD]
[TD="class: xl67, bgcolor: transparent"]75.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PALOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]05-Sep-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]30-Sep-17
[/TD]
[TD="class: xl68, bgcolor: transparent"]36.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PALOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]04-Oct-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]31-Dec-17
[/TD]
[TD="class: xl68, bgcolor: transparent"]42.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PALOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]01-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]05-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]42.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PALOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]08-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]14-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]42.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PALOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]15-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]31-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]42.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]Changed
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BOO1
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEAR/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]05-Sep-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]30-Sep-17
[/TD]
[TD="class: xl68, bgcolor: transparent"]38.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BOO1
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEAR/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]04-Oct-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]31-Dec-17
[/TD]
[TD="class: xl68, bgcolor: transparent"]32.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BOO1
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEAR/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]01-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]05-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]43.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BOO1
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEAR/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]08-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]14-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]45.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]BOO1
[/TD]
[TD="class: xl66, bgcolor: transparent"]AUS
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEAR/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]15-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]31-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]45.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]Changed
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HA01
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]09-Jun-17
[/TD]
[TD="class: xl69, bgcolor: transparent"]30-Jun-17
[/TD]
[TD="class: xl68, bgcolor: transparent"]35.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HA01
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]02-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]05-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]23.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HA01
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]08-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]14-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]32.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]HA01
[/TD]
[TD="class: xl66, bgcolor: transparent"]Vietnam
[/TD]
[TD="class: xl66, bgcolor: transparent"]APPLE/A01
[/TD]
[TD="class: xl69, bgcolor: transparent"]15-Jan-18
[/TD]
[TD="class: xl69, bgcolor: transparent"]31-Jan-18
[/TD]
[TD="class: xl68, bgcolor: transparent"]33.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]Changed
[/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a formula that is able to compare the latest price vs the previous prices. If there is a change in price from the latest vs the previous (regardless of which date). The result column would show changed in the latest price.

Is this possible :(.

Thank you
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
[TABLE="width: 956"]
<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]COUNTRY[/TD]
[TD]ITEM CODE[/TD]
[TD]START[/TD]
[TD]EXPIRE[/TD]
[TD]PRICE[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P01[/TD]
[TD]AUS[/TD]
[TD]APPLE/A01[/TD]
[TD]26-Jan-17[/TD]
[TD]10-Jan-18[/TD]
[TD]75[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P01[/TD]
[TD]AUS[/TD]
[TD]APPLE/A01[/TD]
[TD]11-Jan-18[/TD]
[TD]11-Jan-19[/TD]
[TD]75[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PALOMA[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]05-Sep-17[/TD]
[TD]30-Sep-17[/TD]
[TD]36[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PALOMA[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]04-Oct-17[/TD]
[TD]31-Dec-17[/TD]
[TD]42[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PALOMA[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]01-Jan-18[/TD]
[TD]05-Jan-18[/TD]
[TD]42[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PALOMA[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]08-Jan-18[/TD]
[TD]14-Jan-18[/TD]
[TD]42[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PALOMA[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]15-Jan-18[/TD]
[TD]31-Jan-18[/TD]
[TD]42[/TD]
[TD]Changed[/TD]
[TD]<<<<<<[/TD]
[TD="colspan: 2"]this has not changed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOO1[/TD]
[TD]AUS[/TD]
[TD]PEAR/A01[/TD]
[TD]05-Sep-17[/TD]
[TD]30-Sep-17[/TD]
[TD]38[/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 2"]from the row above[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOO1[/TD]
[TD]AUS[/TD]
[TD]PEAR/A01[/TD]
[TD]04-Oct-17[/TD]
[TD]31-Dec-17[/TD]
[TD]32[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOO1[/TD]
[TD]AUS[/TD]
[TD]PEAR/A01[/TD]
[TD]01-Jan-18[/TD]
[TD]05-Jan-18[/TD]
[TD]43[/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3"]are you asking if the latest price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOO1[/TD]
[TD]AUS[/TD]
[TD]PEAR/A01[/TD]
[TD]08-Jan-18[/TD]
[TD]14-Jan-18[/TD]
[TD]45[/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3"]is different from the first price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOO1[/TD]
[TD]AUS[/TD]
[TD]PEAR/A01[/TD]
[TD]15-Jan-18[/TD]
[TD]31-Jan-18[/TD]
[TD]45[/TD]
[TD]Changed[/TD]
[TD][/TD]
[TD="colspan: 3"]for paloma vietnam apple/a01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HA01[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]09-Jun-17[/TD]
[TD]30-Jun-17[/TD]
[TD]35[/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3"](ie 36 on 5 sept 2017 ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HA01[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]02-Jan-18[/TD]
[TD]05-Jan-18[/TD]
[TD]23[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HA01[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]08-Jan-18[/TD]
[TD]14-Jan-18[/TD]
[TD]32[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HA01[/TD]
[TD]Vietnam[/TD]
[TD]APPLE/A01[/TD]
[TD]15-Jan-18[/TD]
[TD]31-Jan-18[/TD]
[TD]33[/TD]
[TD]Changed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
See if this is what you want.

Excel Workbook
ABCDEFG
1CUSTOMERCOUNTRYITEM CODESTARTEXPIREPRICEResult
2P01AUSAPPLE/A0126-Jan-1710-Jan-1875 
3P01AUSAPPLE/A0111-Jan-1811-Jan-1975
4PALOMAVietnamAPPLE/A015-Sep-1730-Sep-1736
5PALOMAVietnamAPPLE/A014-Oct-1731-Dec-1742
6PALOMAVietnamAPPLE/A011-Jan-185-Jan-1842
7PALOMAVietnamAPPLE/A018-Jan-1814-Jan-1842
8PALOMAVietnamAPPLE/A0115-Jan-1831-Jan-1842Change
9BOO1AUSPEAR/A015-Sep-1730-Sep-1738
10BOO1AUSPEAR/A014-Oct-1731-Dec-1732
11BOO1AUSPEAR/A011-Jan-185-Jan-1843
12BOO1AUSPEAR/A018-Jan-1814-Jan-1845
13BOO1AUSPEAR/A0115-Jan-1831-Jan-1845Change
14HA01VietnamAPPLE/A019-Jun-1730-Jun-1735
15HA01VietnamAPPLE/A012-Jan-185-Jan-1823
16HA01VietnamAPPLE/A018-Jan-1814-Jan-1832
17HA01VietnamAPPLE/A0115-Jan-1831-Jan-1833Change
Changed
 
Upvote 0
Hi Oldbrewer,

The latest price will compare with each previous price. As long as there was a difference in the previous prices it will reflect as changed. Hence for paloma vietnam apple/a01 even though the previous price is the same at 42. But the first price is 36 so it should still reflect as changed.

Hi Peter_SSs,

It worked so far thanks so much :D
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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