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

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
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
ABCD
1List 1
2Employee NameEmployee IDPosition CodeRate of Pay
3John Smith 1
1234567​
10305275​
$ 20.00
4John Smith 2
2345678​
10305276​
$ 20.00
5John Smith 3
3456789​
10305276​
$ 20.00
6John Smith 4
4567890​
10305275​
$ 20.00
7John Smith 5
5678901​
10305276​
$ 21.00

FGHI
1List 2
2Employee NameEmployee IDPosition CodeRate of Pay
3John Smith 1
1234567​
10305276​
$ 21.00
4John Smith 2
2345678​
10305275​
$ 20.00
5John Smith 3
3456789​
10305276​
$ 20.00
6John Smith 4
4567890​
10305275​
$ 20.50
7John Smith 5
5678901​
10305276​
$ 21.00

KLMN
1List 3
2Employee NameEmployee IDPosition CodeRate of Pay
3John Smith 1
1234567​
10305276
$ 21.00
4John Smith 2
2345678​
10305275
$ 20.00
5John 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,990
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

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?

1620915276853.png


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:


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.
 
Last edited:

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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:


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?
 
Last edited:

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
This is what I get:

1620917771110.png



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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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:

 

Forum statistics

Threads
1,144,391
Messages
5,724,075
Members
422,535
Latest member
navjeet

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
Top