Compare Two Lists

AllisterB

Board Regular
Joined
Feb 22, 2019
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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
EFGHIJKLMNOPQ
4List 100List 200
5BranchRoleAppomitedFirst NameLast NameBranchJobFirst NameLast NameAppomited
6AWorker1/03/2020MickeyMouseAWorkerMickeyMouse1/03/2020
7BManager3/06/2019DonaldDuckAManagerGoofyBrown14/03/2018
8AManager14/03/2018GoofyBrownAWorkerJasminOrange18/03/2012
9AWorker1/06/2007JasminOrangeBWorkerHarryHall1/04/2017
10BWorker30/08/2011PopeySailorCManagerJonnyAppleseed31/03/1945
11BWorker1/04/2017HarryHallDWorkerSallyTrout30/12/2018
12CManager31/03/1945JonnyAppleseed
13CWorker11/11/2011DaffyTailor
14
List 2
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673
like this?
BranchRoleAppomitedFirst NameLast NameJobAppomited.1
AWorker01/03/2020MickeyMouseWorker01/03/2020
BManager03/06/2019DonaldDuck
AManager14/03/2018GoofyBrownManager14/03/2018
AWorker01/06/2007JasminOrange
BWorker01/04/2017HarryHallWorker01/04/2017
BWorker30/08/2011PopeySailor
CManager16/05/2027JonnyAppleseed
CWorker11/11/2011DaffyTailor

or post expected result from your example
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673
or like this
BranchRoleAppomitedFirst NameLast NameJobAppomited.1
AWorker01/03/2020MickeyMouseWorker01/03/2020
BManager03/06/2019DonaldDuck
AManager14/03/2018GoofyBrownManager14/03/2018
AWorker01/06/2007JasminOrange
BWorker01/04/2017HarryHallWorker01/04/2017
BWorker30/08/2011PopeySailor
CManager16/05/2027JonnyAppleseed
CWorker11/11/2011DaffyTailor
Worker18/03/2012
Manager31/03/1945
Worker30/12/2018
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
502
Office Version
  1. 365
Platform
  1. Windows
what is your expected results? like this?
1600598476597.png
 
Last edited:

AllisterB

Board Regular
Joined
Feb 22, 2019
Messages
96
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What about Sally trust on list 200
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673

ADVERTISEMENT

you mean this?
BranchRoleAppomitedFirst NameLast NameJobFirst Name.1Last Name.1Appomited.1
AWorker01/03/2020MickeyMouseWorkerMickeyMouse01/03/2020
BManager03/06/2019DonaldDuck
AManager14/03/2018GoofyBrownManagerGoofyBrown14/03/2018
AWorker01/06/2007JasminOrange
BWorker01/04/2017HarryHallWorkerHarryHall01/04/2017
BWorker30/08/2011PopeySailor
CManager16/05/2027JonnyAppleseed
CWorker11/11/2011DaffyTailor
WorkerJasminOrange18/03/2012
ManagerJonnyAppleseed31/03/1945
WorkerSallyTrout30/12/2018

Sally trust doesn't exist :p
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673
or even like this
BranchRoleAppomitedFirst NameLast NameBranch.1JobFirst Name.1Last Name.1Appomited.1
AWorker01/06/2007JasminOrange
BWorker30/08/2011PopeySailor
BManager03/06/2019DonaldDuck
CWorker11/11/2011DaffyTailor
CManager16/05/2027JonnyAppleseed
AWorkerJasminOrange18/03/2012
AManager14/03/2018GoofyBrownAManagerGoofyBrown14/03/2018
AWorker01/03/2020MickeyMouseAWorkerMickeyMouse01/03/2020
BWorker01/04/2017HarryHallBWorkerHarryHall01/04/2017
CManagerJonnyAppleseed31/03/1945
DWorkerSallyTrout30/12/2018
 

AllisterB

Board Regular
Joined
Feb 22, 2019
Messages
96
Office Version
  1. 365
Platform
  1. Windows
This looks helpful - have theXL@BB add-in - but how do I get to see how you did your solution - wher is the ode ? :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,534
Messages
5,523,415
Members
409,520
Latest member
HarryP96

This Week's Hot Topics

Top