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

 

[AllisterB]

Thanks - I like this solution as it puts all details for a comination of Branch and Name on teh one row.
How do I see eth solution - do I use XL2BB ?

Thanks

Allister

 

[sandy666]

You talking to me? (post#10) [Taxi Driver 1976]

btw. XL2BB is for posting data from Excel to the post

 

[AllisterB]

Thanks sandy - the comments were to
shaowu459
- could your solution j have one row per combination of Branch/Person.

I note your comments about use of XL2BB. I know how to use it to post - but for instace how do I see the code/ formula / PQ of a solution that is posted in reponse to my question? I can post teh image but I can't see how the solution works?

Thanks

 

[sandy666]

If user will check formula option in XL2BB you can see formula in the post
if user will post M you will see M, XL2BB doesn't contain any option to post M directly from query

btw. if you are talking to someone in thread with more than one user (except OP) use @ and username , eg. @AllisterB . It will show Alert to this user

and post expected result , will be easier for all around

 

[AllisterB]

@sandy666

Thank you for your very helpful reply.
In regards to your posts 2, 7, 8 etc it is not clear to me if you have have checked teh formula option or the post M option.

Also can you change one of your solutions so that I get each occurance of Branch and Name(first + Last Name) on one row?

Thanks again

 

[sandy666]

and post expected result , will be easier for all around

this is not so hard to do that I think

 

[sandy666]

I am not publishing XL2BB with formula option because I don't use formulas
I am posting XL2BB as table but if you want to see M you need to define which one result you want to see with M

eg.

Book1
	C	D	E
3	2	3	5
Sheet4

Cell Formulas
Range	Formula
E3	E3	=SUM(C3,D3)

for M

Column1	Column2	Addition
2	3	5

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Inserted Sum" = Table.AddColumn(Source, "Addition", each List.Sum({[Column1], [Column2]}), Int64.Type)
in
    #"Inserted Sum"

 

[AllisterB]

I like the look of #7 but ned each combination of Branch and Name (First Name+ Last Name) on a separate row

 

[sandy666]

as I said

post expected result from your example

post expected result , will be easier for all around

 

[shaowu459]

Thanks - I like this solution as it puts all details for a comination of Branch and Name on teh one row.
How do I see eth solution - do I use XL2BB ?

Thanks

Allister

Sorry, i don't have computer around my hand. I will post the M code about 2 hours later. There's a timezone difference