I need help as I need to track changes on my file. I actually have 2 tabs. One is the Old data and the other one is for the new data.
I have a total of 6 columns
Column A B C D and E are for my references
Column F is for Concatenate of Columns A to E
Column G is for Remarks
What I need is for the remarks column to have a remark of "CHANGED"if any of the columns in ABCDE changed New data versus the Old data
"No Changed" if the data in Old data tab and New tab data matched
"New" if the data in New data tab cannot be found in Old data tab
Here's what I have done so far:
CONCATENATE is the column name in the New data tab
=IF(VLOOKUP([@CONCATENATE],'Old data'!F:F,1,0)=[@CONCATENATE], "NO CHANGE", "CHANGED")
Whenever I make changes, N/A is showing as the result
The progress so far is now okay but just need the New remarks for the newly added data.
If the data is neither changed or no changed, meaning not existing in either old tab or new tab, it should return a New value
=ifna(if(iserror(vlookup([@concatenate],'Old data'!G:G,1,0)),"Changed","No Changed"),"NEW")
As stated in the comments VLOOKUP returns an error if the value is not found. And if the criteria in an IF returns an error it will error.
So use ISERROR() to return TRUE/FALSE to the IF:
=IF(ISERROR(VLOOKUP([@CONCATENATE],'Old data'!G:G,1,0)),"CHANGED","NO CHANGE")
Another method that may be a little quicker is to use MATCH:
=IF(ISERROR(MATCH([@CONCATENATE],'Old data'!G:G,0)),"CHANGED","NO CHANGE")
This is my preferred method.