Compare two colums that are the same infor but written differently

[burniksapwet]

Guys I need help from the geniuses of this forum. I need to compare two cells for names with different values. I cant simply change the cell information as they are from ODB connections from two different databases and are listed as such. I want to highlight the rows if the names in column A and column B meets the criteria that I want. I know I have to create multiple "rules" but that is fine with me. So it is basically something like if column a = AGIUSJ and column B = Jake Agius highlight that A and B cell. As you could see on my sample that the row 3 is not highlighted because AGIUSJ did not equal to Jake Agius in column B. I don't want to do a partial name comparison as I will at some point compare different names with no similarities like MODIFICATION in column A and PACKAGE with column B. I would like to thank everyone in advance and hopefully someone can help me out. Thank you.

SUPERVISOR	ASSIGNEE
AGIUSJ	Jake Agius
ISACA	Andrei Isac
AGIUSJ	Andrei Isac
MESSINGJ	Jason Messing
AGIUSJ	Jake Agius

 

[burniksapwet]

You would need a list of the pairings somewhere. Here I have placed the list in columns E:F

21 06 01.xlsm
	A	B	C	D	E	F
1	List 1	List 2
2	Bird	Soil			Book	Novel
3	Big	Large			Modifications	Package
4	Book	Library			Big	Large
5	Book	Novel
6	Package	Modifications
7	Modifications	Package
Compare

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=VLOOKUP($A2,$E$2:$F$4,2,0)=$B2	text	NO

Thank you do much for the help. I got this to work. Now my final question is...is they a way to do this look up for when what you’re comparing repeats? What I mean by that is something like this...
Column A. Column B.
Closed. Completed
Closed. Cancelled

When I use the look up you provided it only works on the first closed which would be paired up with complete. The 2nd one doesn’t get highlighted anymore because I am assuming it is a duplicate. Is it possible to have same date type on column a be compared to multiple data type on column b? Thank you so much to everyone that helped out. We really appreciate this.

 

[Peter_SSs]

Thank you do much for the help. I got this to work

Good news!

.is they a way to do this look up for when what you’re comparing repeats?

Sure, see below.
BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 06 01.xlsm
	A	B	C	D	E	F
1	List 1	List 2
2	Bird	Soil			Book	Novel
3	Big	Large			Book	Library
4	Book	Library			Modifications	Package
5	Book	Novel			Big	Large
6	Package	Modifications
7	Modifications	Package
Compare (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)	text	NO

 

[burniksapwet]

Good news!


Sure, see below.
BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 06 01.xlsm
	A	B	C	D	E	F
1	List 1	List 2
2	Bird	Soil			Book	Novel
3	Big	Large			Book	Library
4	Book	Library			Modifications	Package
5	Book	Novel			Big	Large
6	Package	Modifications
7	Modifications	Package
Compare (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)	text	NO

Thank you for that reply. I have updated my profile to mention what version of excel I have (2016.) I thought that would be my last question but I do have another one just in case i decide to use it this way. Is there a way to reverse that look up? Meaning instead of highlighting the ones that match, it will highlight the ones that are not. Please see if it can still be done with duplicate look ups on column B. It is basically what you have provided but in reverse of that. =COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2). It should now highlight soil bird and Package Modifications. Thank you.

 

[Peter_SSs]

Just add =0 to the end of the previous CF formula:

21 06 01.xlsm
	A	B	C	D	E	F
1	List 1	List 2
2	Bird	Soil			Book	Novel
3	Big	Large			Book	Library
4	Book	Library			Modifications	Package
5	Book	Novel			Big	Large
6	Package	Modifications
7	Modifications	Package
Compare (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)=0	text	NO

 

[burniksapwet]

Just add =0 to the end of the previous CF formula:

21 06 01.xlsm
	A	B	C	D	E	F
1	List 1	List 2
2	Bird	Soil			Book	Novel
3	Big	Large			Book	Library
4	Book	Library			Modifications	Package
5	Book	Novel			Big	Large
6	Package	Modifications
7	Modifications	Package
Compare (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)=0	text	NO

Thank you so much. This is perfect. I think I will go with this type of coding. Now another follow up question. (Sorry for having too many). This also highlight the blanks/empty in column a and b. So it there a way to ignore the blank (null) fields so that it doesn’t highlight it?

Also I have another request just for the sake of having multiple information. Is there a way to filter something as long as the value is available somewhere in column be? Ex

Column a———————-column b
Work Order No.————-Assigned Work
1234—————————-1234,1235,1236
1237—————————-1234, 1235, 1236

would like column a for 1234 and assigned work 1234, 1235, 1236 to highlighted. (Please provide the code to do it in reverse as well)

Thank you so much for all the help everyone has currently provided. Our team really appreciates it.

 

[Peter_SSs]

Change the CF formula to

=AND($A2<>"",$B2<>"",COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)=0)

 

[burniksapwet]

Thank you so much. This is perfect. I think I will go with this type of coding. Now another follow up question. (Sorry for having too many). This also highlight the blanks/empty in column a and b. So it there a way to ignore the blank (null) fields so that it doesn’t highlight it?

Also I have another request just for the sake of having multiple information. Is there a way to filter something as long as the value is available somewhere in column be? Ex

Column a———————-column b
Work Order No.————-Assigned Work
1234—————————-1234,1235,1236
1237—————————-1234, 1235, 1236

would like column a for 1234 and assigned work 1234, 1235, 1236 to highlighted. (Please provide the code to do it in reverse as well)

Thank you so much for all the help everyone has currently provided. Our team really appreciates it.

Change the CF formula to

=AND($A2<>"",$B2<>"",COUNTIFS($E$2:$E$5,$A2,$F$2:$F$5,$B2)=0)

Thank you so much. Really appreciate it. If you are not tired of helping me out I have another request.

Work Order	Assigned Work
1234	1234, 1235, 1236
1235	1235
5678	1234, 1235, 1236
5555	4444
8888	7777,8888

Trying to highlight the rows when nothing on column b matches that of column a when it is separated by a comma or a comma space. Basically I am trying to search for rows fors values listed in column a, doesnt show up in column b even when there are multiple entries in b. Would like to not include blank fields once again. Also I think it will be easy enough to do it in reverse but would like that as well just in case. Thank you so much for all your help.

 

[Peter_SSs]

Try these

burniksapwet.xlsm
	A	B
1	Work Order	Assigned Work
2	1234	1234, 1235, 1236
3	1235	1235
4	5678	1234, 1235, 1236
5
6	5555	4444
7	8888	7777,8888
Compare (4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=ISERROR(FIND(","&$A2&",",","&SUBSTITUTE($B2," ","")&","))	text	NO

burniksapwet.xlsm
	A	B
1	Work Order	Assigned Work
2	1234	1234, 1235, 1236
3	1235	1235
4	5678	1234, 1235, 1236
5
6	5555	4444
7	8888	7777,8888
Compare (5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=AND($A2<>"",$B2<>"",FIND(","&$A2&",",","&SUBSTITUTE($B2," ","")&","))	text	NO

 

[burniksapwet]

Try these

burniksapwet.xlsm
	A	B
1	Work Order	Assigned Work
2	1234	1234, 1235, 1236
3	1235	1235
4	5678	1234, 1235, 1236
5
6	5555	4444
7	8888	7777,8888
Compare (4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=ISERROR(FIND(","&$A2&",",","&SUBSTITUTE($B2," ","")&","))	text	NO

burniksapwet.xlsm
	A	B
1	Work Order	Assigned Work
2	1234	1234, 1235, 1236
3	1235	1235
4	5678	1234, 1235, 1236
5
6	5555	4444
7	8888	7777,8888
Compare (5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=AND($A2<>"",$B2<>"",FIND(","&$A2&",",","&SUBSTITUTE($B2," ","")&","))	text	NO

It works but I found out something. The problem in this link is what I am experiencing. Why Does Excel Say Cell Values Are Not Equal When They Are? — TheSpreadsheetGuru.
When I tried to manually create a sample comparison to test the query you provided worked. But like I have initially mentioned all this are databases queries and I have no control on how the information is exported. It seems like some of the columns fields I am comparing are not the same. Some columns I need to compare seems to be to be to a text field to a numeric field. when you compare both columns it is coming out as false even though the content value is the same. I think at one point a text field comparison to another text field didn’t work as well. Don’t want to convert it all the time as when I refresh my query to pull new information from the database everything resets. Is there a way to fix this like how they proposed on the link? Which is to convert the query to a value search it looks like is the solution. I guess it’s doing a comparison in the value (which is what I need) rather than what the field is (numeric, text field) I will try to upload a excel file that has the problematic values so the it can be evaluated. Thank you.

 

[Peter_SSs]

I will try to upload a excel file that has the problematic values so the it can be evaluated.

That might be useful since the link you provided discusses a number of different possibilities so it is hard to draw any definite proposal in relation to your data which we really know nothing about.