Compare Two Lists

[AllisterB]

I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the web.
I need to be able to know for each Person (First name +las tname) at a Branch, the differences in the Role and Date Appointed between the two Lists.
It is possible for a Branch that the Person is on one List but not on the other.
[Once the differences are identified the user will check what is the correct Information and make changes to the lists to get them to match]

I have tried using PQ Merge but couldn't get this to work.

Has anyone got any ideas

Thanks

Allister

Book2
	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	List 100							List 200
5	Branch	Role	Appomited	First Name	Last Name			Branch	Job	First Name	Last Name	Appomited
6	A	Worker	1/03/2020	Mickey	Mouse			A	Worker	Mickey	Mouse	1/03/2020
7	B	Manager	3/06/2019	Donald	Duck			A	Manager	Goofy	Brown	14/03/2018
8	A	Manager	14/03/2018	Goofy	Brown			A	Worker	Jasmin	Orange	18/03/2012
9	A	Worker	1/06/2007	Jasmin	Orange			B	Worker	Harry	Hall	1/04/2017
10	B	Worker	30/08/2011	Popey	Sailor			C	Manager	Jonny	Appleseed	31/03/1945
11	B	Worker	1/04/2017	Harry	Hall			D	Worker	Sally	Trout	30/12/2018
12	C	Manager	31/03/1945	Jonny	Appleseed
13	C	Worker	11/11/2011	Daffy	Tailor
14
List 2

 

[sandy666]

like this?

Branch	Role	Appomited	First Name	Last Name	Job	Appomited.1
A	Worker	01/03/2020	Mickey	Mouse	Worker	01/03/2020
B	Manager	03/06/2019	Donald	Duck
A	Manager	14/03/2018	Goofy	Brown	Manager	14/03/2018
A	Worker	01/06/2007	Jasmin	Orange
B	Worker	01/04/2017	Harry	Hall	Worker	01/04/2017
B	Worker	30/08/2011	Popey	Sailor
C	Manager	16/05/2027	Jonny	Appleseed
C	Worker	11/11/2011	Daffy	Tailor

or post expected result from your example

 

[sandy666]

or like this

Branch	Role	Appomited	First Name	Last Name	Job	Appomited.1
A	Worker	01/03/2020	Mickey	Mouse	Worker	01/03/2020
B	Manager	03/06/2019	Donald	Duck
A	Manager	14/03/2018	Goofy	Brown	Manager	14/03/2018
A	Worker	01/06/2007	Jasmin	Orange
B	Worker	01/04/2017	Harry	Hall	Worker	01/04/2017
B	Worker	30/08/2011	Popey	Sailor
C	Manager	16/05/2027	Jonny	Appleseed
C	Worker	11/11/2011	Daffy	Tailor
					Worker	18/03/2012
					Manager	31/03/1945
					Worker	30/12/2018

 

[shaowu459]

what is your expected results? like this?

 

[AllisterB]

What about Sally trust on list 200

 

[AllisterB]

what is your expected results? like this?
View attachment 22728
How could you automate the identification of the differences

 

[sandy666]

you mean this?

Branch	Role	Appomited	First Name	Last Name	Job	First Name.1	Last Name.1	Appomited.1
A	Worker	01/03/2020	Mickey	Mouse	Worker	Mickey	Mouse	01/03/2020
B	Manager	03/06/2019	Donald	Duck
A	Manager	14/03/2018	Goofy	Brown	Manager	Goofy	Brown	14/03/2018
A	Worker	01/06/2007	Jasmin	Orange
B	Worker	01/04/2017	Harry	Hall	Worker	Harry	Hall	01/04/2017
B	Worker	30/08/2011	Popey	Sailor
C	Manager	16/05/2027	Jonny	Appleseed
C	Worker	11/11/2011	Daffy	Tailor
					Worker	Jasmin	Orange	18/03/2012
					Manager	Jonny	Appleseed	31/03/1945
					Worker	Sally	Trout	30/12/2018

Sally trust doesn't exist

 

[sandy666]

or even like this

Branch	Role	Appomited	First Name	Last Name	Branch.1	Job	First Name.1	Last Name.1	Appomited.1
A	Worker	01/06/2007	Jasmin	Orange
B	Worker	30/08/2011	Popey	Sailor
B	Manager	03/06/2019	Donald	Duck
C	Worker	11/11/2011	Daffy	Tailor
C	Manager	16/05/2027	Jonny	Appleseed
					A	Worker	Jasmin	Orange	18/03/2012
A	Manager	14/03/2018	Goofy	Brown	A	Manager	Goofy	Brown	14/03/2018
A	Worker	01/03/2020	Mickey	Mouse	A	Worker	Mickey	Mouse	01/03/2020
B	Worker	01/04/2017	Harry	Hall	B	Worker	Harry	Hall	01/04/2017
					C	Manager	Jonny	Appleseed	31/03/1945
					D	Worker	Sally	Trout	30/12/2018

 

[shaowu459]

What about Sally trust on list 200

Add a column in PQ to show the differences, like "the date is different/the role is different, please check".

 

[AllisterB]

This looks helpful - have theXL@BB add-in - but how do I get to see how you did your solution - wher is the ode ?