Finding Changes and New Additions to Dataset

[Esscaster]

Hi All,
New to forum and I guess an intermediate Excel user, never coded and still don't know how to use VLookup although good with Pivots, sorting, and mathematical formulas, etc.

Anyway, wondering how to compare two data sets. They are taken from the same system and contain the same headers and types of information, but they are taken in different points in time in which new entries are added and information in any given field can be changed.

Looking for a way to find these new entries as well as any entries that have any changes to any of the fields.

For example:

if data set A looks like this

1. A Red
2. B orange
3. C yellow
4. D green
5. E blue

and data set B looks like this:

1. A Red
2. B orange
3. C yellow
4. D violet (changed)
5. E blue
6. F (new) Indigo

... where row 6 is new and the entry for cell 4b has changed from green to violet.

Looking a way to easily flag new entries as well as find changes. The datasets I work with have a few hundred entries and about 20 columns.

Thanks in advance for help!

 

[sergioMabres]

Hi Ess
For your sample data one way to do (there are many) would be using Lookup

Hoja2

*	A	B	C	D	E	F
Key	Value	*	Key	Value	Flag
A	Red	*	A	Red	*
B	orange	*	B	orange	*
C	yellow	*	C	yellow	*
D	green	*	D	violet	Changed
E	blue	*	E	blue	*
*	*	*	F	Indigo	New

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

F2 is =IF(IFERROR(VLOOKUP(D2,$A$2:$B$6,2,0),-999)=-999,"New",IF(VLOOKUP(D2,$A$2:$B$6,2,0)=E2,"","Changed"))

Cheers
Sergio

 

[shyy]

If both worksheets are in the exact same order and same format you can just make the first cell equal to the second. This will return either TRUE/FALSE. I make the FALSE red color to show the different.

=Sheet1!A1=Sheet2!A1

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

I would do a combination of an INDEX/MATCH along with IF(ISNA and IF(ISERR

The index match would be something along the lines of:

=INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))

Since you want to look up multiple values, it will return the values in B if it finds them. If if finds part of the value but not the other then it will return an #N/A, so we throw in a IF(ISNA

=IF(ISNA(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"Value Changed",INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0)))

This will let you know that a Value has changed...

Lastly, if it does not find the values, then it will throw out an #VALUE... so now we have to do a check for that, with IF(ISERR (which excludes #N/A)...

=IF(ISNA(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"Value Changed",IF(ISERR(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"New Values",INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))))

I know it looks like a lot, but the IFs make it redundant... someone else might come up with something more elegant, but that should get the job done!

 

[Esscaster]

Welcome to the Mr. Excel Message Board!

I would do a combination of an INDEX/MATCH along with IF(ISNA and IF(ISERR

The index match would be something along the lines of:

=INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))

Since you want to look up multiple values, it will return the values in B if it finds them. If if finds part of the value but not the other then it will return an #N/A, so we throw in a IF(ISNA

=IF(ISNA(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"Value Changed",INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0)))

This will let you know that a Value has changed...

Lastly, if it does not find the values, then it will throw out an #VALUE... so now we have to do a check for that, with IF(ISERR (which excludes #N/A)...

=IF(ISNA(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"Value Changed",IF(ISERR(INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))),"New Values",INDEX($B$2:$B$6,MATCH(D2,IF($B$2:$B$6=E2,$A$2:$A$6),0))))

I know it looks like a lot, but the IFs make it redundant... someone else might come up with something more elegant, but that should get the job done!

Thanks I'll give that a go

 

[Esscaster]

Used countif successfully to find new entries. Was a lot easier than I thought, have an idea with countif with finding changes...