Need VBA code to Match 5 columns and mark unchanged/changed in next cell

[billigee]

Dear All,

I am working with a sheet where there is a cost of products for 5 months from column A to E, I want to check if there is any change in price in any month then it should mention CHANGED in column F or UNCHANGED in column F

the criteria is TRUE figure (UNCHANGED) is

if amount in all 5 columns is same
if amount of 1 column is blank or zero and other 4 column are same
if amount of more than 1 column is blank or zero and rest of columns are same

Then the result should be UNCHANGED

otherwise it should mentioned CHANGED

to make it more clear see snapshot

Thankyou

 

[My Aswer Is This]

You know what's worse than missing things like that on a page of posts (which is just oversight that we all do from time to time)? The forum design where the first post is at the top of subsequent pages. You land on page 2 and see the 1st post at the top. It's information or questions that are likely not initially phrased properly, or just wrong - like that chart. Anyone landing there starts off with the wrong information. I always have to go back and find out what the last post was when I should be landing there instead. It's a pet peeve of mine but I know I'll have to live with it.

Yes, I agree it is confusing me also. I always have to go back to first posting and step through everyone to see what's going on in its entirety. Take care and have a nice weekend.

 

[Peter_SSs]

I came up with different approach. Try to count unique price >0, if >1 then CHANGED
In F1, use UDF:
=PriceChange(A1:E1)
with below code:

If interested, there is no need for the 'count' variable in your function (since the dictionary object has one built-in) or to check every time whether the value is already in the dictionary. So the UDF could be written as

Function PriceChange(ByVal rng As Range)
  Dim cell As Range

  With CreateObject("scripting.dictionary")
    For Each cell In rng
      If cell.Value > 0 Then .Item(cell.Value) = ""
    Next
    PriceChange = IIf(.Count = 1, "UN", "") & "CHANGED"
  End With
End Function

Having said that, I don't see any point in using a udf when a direct worksheet formula is possible since worksheet formulas they are generally more efficient than udfs.

 

[billigee]

Oh wow

First I want to thank all of you who spend time to make it and you know every code is working for me

@Micron its awesome working

@bebo021999 I know its fantastic but I am really sorry to share that its giving me Value error ( I am sure your function will work anyhow and may be I am not using it correctly) please see the snapshot so you can better understand

 

[Peter_SSs]

I want to thank all of you who spend time to make it and you know every code is working for me

@bebo021999 I know its fantastic but I am really sorry to share that its giving me Value error

That seems contradictory to me! Surely giving an error is not working?

@Micron its awesome working

Assuming that means you are using the user-defined function from post #15, why not use (or at least try) a standard worksheet function (usually more efficient) as suggested in post #13?

 

[billigee]

That seems contradictory to me! Surely giving an error is not working?


Assuming that means you are using the user-defined function from post #15, why not use (or at least try) a standard worksheet function (usually more efficient) as suggested in post #13?

I tried it (post 13) and working fine i have given a like to it
And for small data it is best i guess

 

[billigee]

That seems contradictory to me! Surely giving an error is not working?


Assuming that means you are using the user-defined function from post #15, why not use (or at least try) a standard worksheet function (usually more efficient) as suggested in post #13?

And secondly I am using 365 but at my work we are using 2016 so i guess it will not work there right?
Correct me if I am wrong
UDF can be use in 2016 version also

And once again I am thankfull to you for providing me with this solution

 

[Peter_SSs]

I tried it (post 13) and working fine i have given a like to it

Many people 'Like' a post without even trying it, or they try it and it doesn't work but they still like it - trying to be friendly I guess. The result though is that you can't really tell anything from a Like - especially when you have specifically referred to other suggestions.

And secondly I am using 365 but at my work we are using 2016 so i guess it will not work there right?

That is correct, but since your profile has 365 I made a suggestion for that. With Excel 2016 you can still do it with standard worksheet functions.

billigee.xlsm
	A	B	C	D	E	F
2	5	5	5	5	5	UNCHANGED
3	5		5	0	5	UNCHANGED
4	5	0	0	0	0	UNCHANGED
5	0	5	5			UNCHANGED
6	0	5	6	0	0	CHANGED
7				5		UNCHANGED
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=IF(MAX(A2:E2)=AGGREGATE(15,6,A2:E2/(A2:E2>0),1),"UNCHANGED","CHANGED")

 

[Micron]

That is correct, but since your profile has 365 I made a suggestion for that. With Excel 2016 you can still do it with standard worksheet functions.

Which is it that you're saying can't be used in 2016 - the proposed formula or the udf?
I did a quick search and found that udf will work in 2016 but I'm not certain what you guys are saying.

 

[billigee]

=IF(MAX(A2:E2)=AGGREGATE(15,6,A2:E2/(A2:E2>0),1),"UNCHANGED","CHANGED")

I will check it in my 2016 version and hope it will work, honestly I thought it can only be done through VBA but you bring standard formula and it does the Job, I was not even thinking of the UDF so thank you so much for this Let and Filter function and your VBA code also worked for me.

 

[billigee]

Which is it that you're saying can't be used in 2016 - the proposed formula or the udf?

LET Function in 2016