Compare lists and generate new list with what is different from the first list

[bh24524]

Hello, as I mentioned last week I would be posting this. This is very similar to my post last week about comparing two lists and generating a third list of items that were missing. Well this time, I am looking to compare two lists and generate a third list of what is different from List 1. Periodically, we do audits on employee information between two systems to make sure they match. There are actually two different audits that we do but if I can get a solution for this one, then I can apply that the second one which has different fields, but the same number of comparisons.

SCENARIO

	A	B	C	D
1	List 1
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305275​	$ 20.00
4	John Smith 2	2345678​	10305276​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.00
7	John Smith 5	5678901​	10305276​	$ 21.00

	F	G	H	I
1	List 2
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 3	3456789​	10305276​	$ 20.00
6	John Smith 4	4567890​	10305275​	$ 20.50
7	John Smith 5	5678901​	10305276​	$ 21.00

	K	L	M	N
1	List 3
2	Employee Name	Employee ID	Position Code	Rate of Pay
3	John Smith 1	1234567​	10305276​	$ 21.00
4	John Smith 2	2345678​	10305275​	$ 20.00
5	John Smith 4	4567890​	10305275​	$ 20.50

We are doing an audit between List 1 and 2 and looking for differences in position codes and/or pay rates. List 3 has the info of those who have something different between the two lists and lists them accordingly. John Smith1 has a different position code and a different pay rate. John Smith2 has a different position code but his pay rate is the same. John Smith4 has the same position code between the two lists but a different pay rate. So their names would be generated because one or BOTH of those fields has something different from the first list.

I'm pretty sure the formula here would utilize Aggregate but that is a function I am not at all familiar with so I definitely need help on that.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Compare two lists and generate a new list with what is different from the first list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Oddball2020]

Still looking for help on this. Can't figure out these solutions above. Any help?

Part of the problem is that you can't create a truncated list of List 1 to List 2 comparisons using only formula on the same worksheet like that (not that I'm aware) You can auto-fill a formula down, but you'd get blanks (or #N/As depending how you set up your formula) for the List1-List2 checks that are ok, or the formula displays the disconnects/errors. You could then filter out the List 3 blanks, but that's a bit of manual intervention.

Maybe you could use RANK along with a flag of some sort and return the flagged disconnect results in rank order, based on the employee ID number? Then you may be able to get a truncated list type of effect only using formula.

The possible solution is to use VBA to assist in cleaning up the presentation so you'd end up with a truncated List 3 only showing the disconnects between Lists 1 & 2.

I'm not aware of some built-in wizard or other feature that would do this automatically, but I'm sure someone has made an add-on that does something like it.

I haven't looked at your formula above yet, sorry, will think about this and try yours out tomorrow.

 

[bh24524]

I'm willing to even just do a one comparison at a time situation if that will make it simpler because then its just a matter of pasting any additional data I want to compare over top of it. So just have Name, Employee ID, and then the position code for the first and then replace the position code with pay rate once that was done. Would that change anything?

 

[Oddball2020]

Did you already try and tweak what I posted on monday?

Col H: =IF(VLOOKUP($K4,$F:$I,3,0)<>VLOOKUP($A4,$A:$D,3,0),VLOOKUP($F4,$F:$I,3,0),"")
Col I: =IF(VLOOKUP($K4,$F:$I,4,0)<>VLOOKUP($A4,$A:$D,4,0),VLOOKUP($F4,$F:$I,4,0),"")

And auto-fill those down the length of your List 1, then filter out the blanks?

Also didn't previous notice:
"This is very similar to my post last week about comparing two lists and generating a third list of items that were missing"
Are you now comparing between List 1 & 2 but in the other direction than last time?

 

[bh24524]

I'm confused. Column H and I are where I am pasting a second set of data. I shouldn't have formulas in there I wouldn't think. Shouldn't they be in L thru N? Also, does your formula require the pasted contents to be in the same row as I have here?

Because they are only in the same row because I took the time to pick them out, sort them, and paste where needed. I don't want to have to do that every time as it would really just be the equivalent to the conditional formatting audit process I mentioned before that I currently use for it.

In this post:

Compare two lists and generate a new list of items missing from one list

Hello, so I learned how to do this in Google Sheets the other day. It was as the title says a comparison between two lists that I wanted a formula to list all the items from the first list that were NOT contained on the second list. I know you can do this a different way utilizing conditional...

www.mrexcel.com

This was to generate items that were MISSING from the first list to the far left. That list never changes, it only gets added to. It's the middle list which is pasted and then the 3rd far right list is what shows what is not in the first list. The nice thing about that list is I don't have to sort anything that I paste. I paste and the results populate. It's somewhat similar to this request. The difference with this request is BOTH sets of data are pasted but the data in the middle is the data we ourselves maintain and basically submit up to make changes in the system. The changes made are reflected in the First list of data. We are auditing against that to make sure the changes have been properly made. Ideally as with the first and similar forum post, I'd like to do just that and not have to do any sorting, and have it populate what is different. I figure it has to somehow be possible with the Aggregate function maybe, but I'm not familiar with that function and the explanations given on it weren't clear to me.

 

[Oddball2020]

I'm confused. Column H and I are where I am pasting a second set of data. I shouldn't have formulas in there I wouldn't think. Shouldn't they be in L thru N? Also, does your formula require the pasted contents to be in the same row as I have here?

View attachment 38713

Because they are only in the same row because I took the time to pick them out, sort them, and paste where needed. I don't want to have to do that every time as it would really just be the equivalent to the conditional formatting audit process I mentioned before that I currently use for it.

In this post:

Compare two lists and generate a new list of items missing from one list

Hello, so I learned how to do this in Google Sheets the other day. It was as the title says a comparison between two lists that I wanted a formula to list all the items from the first list that were NOT contained on the second list. I know you can do this a different way utilizing conditional...

www.mrexcel.com

This was to generate items that were MISSING from the first list to the far left. That list never changes, it only gets added to. It's the middle list which is pasted and then the 3rd far right list is what shows what is not in the first list. The nice thing about that list is I don't have to sort anything that I paste. I paste and the results populate. It's somewhat similar to this request. The difference with this request is BOTH sets of data are pasted. Ideally as with the first, I'd like to do just that and not have to do any sorting, and have it populate what is different. I figure it has to somehow be possible with the Aggregate function maybe, but I'm not familiar with that function and the explanations given on it weren't clear to me.

Sorry, paste those into Col M & N for your List 3.

The VLOOKUP is looking for the List 3 name in List 1 and 2, so the order shouldn't matter. I tweaked this just now because I did notice something was slightly off:

M4: =IF(VLOOKUP($K4,$F:$I,3,0)<>VLOOKUP($K4,$A:$D,3,0),VLOOKUP($F4,$F:$I,3,0),"")
N4: =IF(VLOOKUP($K4,$F:$I,4,0)<>VLOOKUP($K4,$A:$D,4,0),VLOOKUP($F4,$F:$I,4,0),"")
K4: = A4 .... I added this so the List 3 comparasion order was the same as the List 1 order of names; I don't think I mentioned that before and it was probably gunking up your results, sorry!
L4: =VLOOKUP(K4,A:D,2,0) ... this is for the employee ID which from how you presented it, was identical and didn't need to be also checked?

 

[bh24524]

Okay re-read what I just posted when able because I actually did edit it slightly. Idk if the edits might change what you want to do.

 

[Oddball2020]

Okay re-read what I just posted when able because I actually did edit it slightly. Idk if the edits might change what you want to do.

I went back to your other posting where you provided the data and AGGREGATE formula. I transposed everything but the formula won't display/return anything below row 3 (even tried as array { } )

I suppose it was suggested to try AGGREGATE because it had lots of options to skip over hidden, blank, error cells etc. The funtion 15 is a rank order SMALL, similar to what I was thinking of.

Also, if your Employee Name is not consistent between List 1 & 2, then the VLOOKUP approach would need to change and everything use the Employee ID number as the cross-reference, and actually include to check for name consistency?

 

[bh24524]

This is what I get:

I think we're making progress and that this is what I am after. I do have a question though, is it possible to somehow only have the names and ID's display if there are actual variances shown in either M or N?

in K3, I tried

=if(M3="","",A3)

But got a circular reference warning. If we can eliminate that somehow and have it only show when there are actual changes, then it would be functioning very similar to the Aggregate funciton of the related/referenced post.

 

[Oddball2020]

This is what I get:

View attachment 38723


I think we're making progress and that this is what I am after. I do have a question though, is it possible to somehow only have the names and ID's display if there are actual variances shown in either M or N?

in K3, I tried

=if(M3="","",A3)

But got a circular reference warning. If we can eliminate that somehow and have it only show when there are actual changes, then it would be functioning very similar to the Aggregate funciton of the related/referenced post.

Yeah I've been looking into that aspect of it (only showing the disconnects). Do you have excel 365? If so, there's a new FILTER formula that only displays list info if criteria is met:

FILTER Function

The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the...

exceljet.net