How To Compare Two Columns In Excel?

[Gigiwan]

Hi
Here's a scenario I'm facing:

I have a data set for 2022 and 2023, both of which contain the identical table pattern for employee system responsibility, but in 2023, some of the given employee system responsibility are removed/added to them.

How do I find the difference between the two tables for each user?

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E
1	Employees data for 2023			Employees data for 2023
2	Name	Responsibility		Name	Responsibility
3	User1	AB		User1	AC
4	User1	AC		User1	AN
5	User1	AN		User1	DE
6	User1	AE		User1	AW
7	User1	AW		User1	AT
8	User1	AT		User1	AL
9	User1	AL		User1	AM
10	User1	AM		User1	WQ
11	User1	AA		User1	AR
12	User1	AR		User1	AY
13	User1	AY		User1	OI
14	User1	AO		User1	AP
15	User1	AP		User1	AN
16	User2	AN		User2	PO
17	User2	XK		User2	BN
18	User2	BN		User2	WE
19	User2	AE		User2	AW
20	User2	AW		User2	OK
21	User2	OK		User2	DS
22	User2	AL		User2	OP
23	User2	OP		User2	AA
24	User2	AA		User2	WX
25	User2	AR		User2	LR
26	User2	LR		User2	YB
27	User2	AO		User2	MU
28	User2	MU		User2	CD
29	User3	CD		User3	GB
30	User3	GB		User3	JU
31	User3	JU		User3	IK
32	User3	IK		User3	LO
33	User3	LO		User3	FR
34	User3	FR		User3	WS
35	User3	WS		User3	WE
36	User3	WE		User3	YT
37	User3	YT		User3	UY
38	User3	UY		User3	IK
39	User3	IK		User3	VC
40	User3	VC		User3	NB
41	User3	NB		User3	MJ
42	User3	MJ		User3	LK
43	User3	LK		User3	JH
44	User4	JH		User4	CH
45	User4	CH		User4	OU
46	User4	QB		User4	PD
47	User4	PD		User4	BG
48	User4	ZO		User4	RN
49	User4	RN		User4	UM
50	User4	UM		User4	CY
51	User4	CY		User4	DC
52	User4	WB		User4	CJ
53	User4	CJ		User4	hy
54	User4	hy		User4	we
55	User4	we		User4	HN
56	User4	hy		User4	gt
57	User4	gt		User4	bg
58	User4	bg		User4	dc
59	User4	dc		User4	UZ
60	User4	xs		User4	po
61	User4	po		User4	YU
62	User5	YU		User5	TY
63	User5	TY		User5	ZA
64	User5	ZA		User5	CX
65	User5	CX		User5	BG
66	User5	BG		User5	KI
67	User5	KI		User5	UY
68	User5	UY		User5	TR
69	User5	TR		User5	ER
70	User5	ER		User5	VT
71	User5	QW		User5	XS
72	User5	XS		User5	VF
73	User5	VF		User5	OL
74	User5	OL		User5	QW
75	User5	QW		User5	UY
76	User5	UY		User5	JH
77	User5	JH		User5	BH
78	User5	BH		User5	VF
79	User5	VF		User5	CD
80	User5	CD		User5
Sheet1

 

[Gigiwan]

Any idea about the formula?

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(Source, {"Name","Responsibility"},Source2, {"Name","Responsibility"}, "TBL", JoinKind.FullOuter),
    ExpandedTBL = Table.ExpandTableColumn(MQ, "TBL", {"Name", "Responsibility"}, {"Name.1", "Responsibility.1"})
in  ExpandedTBL

 

[Gigiwan]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(Source, {"Name","Responsibility"},Source2, {"Name","Responsibility"}, "TBL", JoinKind.FullOuter),
    ExpandedTBL = Table.ExpandTableColumn(MQ, "TBL", {"Name", "Responsibility"}, {"Name.1", "Responsibility.1"})
in  ExpandedTBL

Tyvm sir, that worked for me!