Compare rows between two sources and output yes/no for matching

[tuong_ng89]

Hello all,


I have about close to 3000 rows of data I need to compare and find mismatch and I'm in need of a macro or anything that can help me get the result in a timely manner.

Quick summary. I have users from source A and source B. Each users are assigned a repo and a role in each source. The goal is to find the mismatch between the user's assigned repo and their role. Below is a sample table I did manually. There will be some duplicates but that's fine. I can filter that out later. There are supposed to be empty cell for B Source if A source has text and vice versa. What I'm aiming for is to output the red and blue fills where there's a mismatch in either role, repo A or repo B.

Source A and B Discrepencies.xlsx
	A	B	C	D	E	F	G	H
1	Source	Username	Repo	Role in Manual Source	Role in Automatic Source	Role Match	Repo in Manual Source	Repo in Automatic Source
2	Manual	Alpha	Angel	End User		Yes	Yes	Yes
3	Manual	Alpha	Beltane	Planning User		Yes	Yes	Yes
4	Manual	Alpha	Angel	End User		Yes	Yes	Yes
5	Manual	Alpha	Angel	End User		Yes	Yes	Yes
6	Automated	Alpha	Angel		End User	Yes	Yes	Yes
7	Automated	Alpha	Beltane		Planning User	Yes	Yes	Yes
8	Manual	Bravo	Angel	End User		Yes	Yes	Yes
9	Manual	Bravo	Angel	End User		Yes	Yes	Yes
10	Manual	Bravo	Angel	End User		Yes	Yes	Yes
11	Automated	Bravo	Angel		End User	Yes	Yes	Yes
12	Manual	Charlie	Chakra	End User		Yes	Yes	Yes
13	Automated	Charlie	Divination		End User	No	No	Yes
14	Automated	Charlie	Enlightenment		End User	No	No	Yes
15	Automated	Charlie	Filioque		Planning User	No	No	Yes
16	Manual	Delta	Chakra	End User		No	Yes	No
17	Automated	Delta	Divination		End User	No	No	Yes
18	Automated	Delta	Enlightenment		End User	No	No	Yes
19	Automated	Delta	Filioque		Planning User	No	No	Yes
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:H19	Cell Value	contains "Yes"	text	NO
F1:H19	Cell Value	contains "No"	text	NO
A1:A19	Cell Value	contains "Manual"	text	NO
A1:A19	Cell Value	contains "Automated"	text	NO

 

[Peter_SSs]

Welcome to the MrExcel board!

This is my attempt but perhaps I am not understanding correctly?
My results mostly agree with yours but can you explain why the two orange cells should be "Yes" as shown in your results?

25 02 19.xlsm
	A	B	C	D	E	F	G	H
1	Source	Username	Repo	Role in Manual Source	Role in Automatic Source	Role Match	Repo in Manual Source	Repo in Automatic Source
2	Manual	Alpha	Angel	End User		Yes	Yes	Yes
3	Manual	Alpha	Beltane	Planning User		Yes	Yes	Yes
4	Manual	Alpha	Angel	End User		Yes	Yes	Yes
5	Manual	Alpha	Angel	End User		Yes	Yes	Yes
6	Automated	Alpha	Angel		End User	Yes	Yes	Yes
7	Automated	Alpha	Beltane		Planning User	Yes	Yes	Yes
8	Manual	Bravo	Angel	End User		Yes	Yes	Yes
9	Manual	Bravo	Angel	End User		Yes	Yes	Yes
10	Manual	Bravo	Angel	End User		Yes	Yes	Yes
11	Automated	Bravo	Angel		End User	Yes	Yes	Yes
12	Manual	Charlie	Chakra	End User		No	Yes	No
13	Automated	Charlie	Divination		End User	No	No	Yes
14	Automated	Charlie	Enlightenment		End User	No	No	Yes
15	Automated	Charlie	Filioque		Planning User	No	No	Yes
16	Manual	Delta	Chakra	End User		No	Yes	No
17	Automated	Delta	Divination		End User	No	No	Yes
18	Automated	Delta	Enlightenment		End User	No	No	Yes
19	Automated	Delta	Filioque		Planning User	No	No	Yes
Compare

Cell Formulas
Range	Formula
F2:F19	F2	=IF(COUNTIFS(A$2:A$19,IF(A2="Manual","Automated","Manual"),B$2:B$19,B2,C$2:C$19,C2,IF(D2="",D$2:D$19,E$2:E$19),D2&E2),"Yes","No")
G2:G19	G2	=IF(COUNTIFS(A$2:A$19,"Manual",B$2:B$19,B2,C$2:C$19,C2),"Yes","No")
H2:H19	H2	=IF(COUNTIFS(A$2:A$19,"Automated",B$2:B$19,B2,C$2:C$19,C2),"Yes","No")

 

[tuong_ng89]

Welcome to the MrExcel board!

This is my attempt but perhaps I am not understanding correctly?
My results mostly agree with yours but can you explain why the two orange cells should be "Yes" as shown in your results?

25 02 19.xlsm
	A	B	C	D	E	F	G	H
1	Source	Username	Repo	Role in Manual Source	Role in Automatic Source	Role Match	Repo in Manual Source	Repo in Automatic Source
2	Manual	Alpha	Angel	End User		Yes	Yes	Yes
3	Manual	Alpha	Beltane	Planning User		Yes	Yes	Yes
4	Manual	Alpha	Angel	End User		Yes	Yes	Yes
5	Manual	Alpha	Angel	End User		Yes	Yes	Yes
6	Automated	Alpha	Angel		End User	Yes	Yes	Yes
7	Automated	Alpha	Beltane		Planning User	Yes	Yes	Yes
8	Manual	Bravo	Angel	End User		Yes	Yes	Yes
9	Manual	Bravo	Angel	End User		Yes	Yes	Yes
10	Manual	Bravo	Angel	End User		Yes	Yes	Yes
11	Automated	Bravo	Angel		End User	Yes	Yes	Yes
12	Manual	Charlie	Chakra	End User		No	Yes	No
13	Automated	Charlie	Divination		End User	No	No	Yes
14	Automated	Charlie	Enlightenment		End User	No	No	Yes
15	Automated	Charlie	Filioque		Planning User	No	No	Yes
16	Manual	Delta	Chakra	End User		No	Yes	No
17	Automated	Delta	Divination		End User	No	No	Yes
18	Automated	Delta	Enlightenment		End User	No	No	Yes
19	Automated	Delta	Filioque		Planning User	No	No	Yes
Compare

Cell Formulas
Range	Formula
F2:F19	F2	=IF(COUNTIFS(A$2:A$19,IF(A2="Manual","Automated","Manual"),B$2:B$19,B2,C$2:C$19,C2,IF(D2="",D$2:D$19,E$2:E$19),D2&E2),"Yes","No")
G2:G19	G2	=IF(COUNTIFS(A$2:A$19,"Manual",B$2:B$19,B2,C$2:C$19,C2),"Yes","No")
H2:H19	H2	=IF(COUNTIFS(A$2:A$19,"Automated",B$2:B$19,B2,C$2:C$19,C2),"Yes","No")

This is exactly why I needed a script/macro to help haha. That was a misread on my part and I made an error. Imagine having to do this manually for 3000 rows. Your attempt was correct!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.