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]

My deepest apologies sir. Your quires have all been working properly after rechecking all of it again today. It must have been because I was still working on this late at night and I was getting a little bit tired that caused me to either make a mistake or I was not paying attention to much to what I was doing. Now lastly I just noticed something that staff was doing after examining the result set of the quires so I will need help updating the last two queries you have created for me. They would sometimes add like a word in front. Mind updating the last two queries please to include that 2015-25 and MOC 2015-25 is equal and so are the rest? Below is a sample of what they are doing. Once again I am very grateful for the help that you have provided us. Thank you so much.

2015-25	MOC 2015-25
2015-33	WORK ORDER 2015-33
2016-08	CONTRACT 2016-08

 

[Peter_SSs]

Could we have a more comprehensive list of all the various data forms that might exist in either column and a clear indication of exactly what results are expected. I am becoming confused by all the incremental changes.

 

[burniksapwet]

Filtered columns M and Q (purple highlight) with your code. Works perfectly except that it doesn't capture if staff decides to put alpha characters prior to the number like MOC RFB etc etc. Would like the code to be able to catch that as long as the numeric value is present. So essentially it is the same as the code you created with the comma separator but adds on to any alpha characters that staff may decide to add. Thank yo so much. Sorry if I have been asking this partially as I did not really know what to do initially with the data that was provided to me. Thank you so much. I also sent you a pm (start a conversation) that have the link to the actual excel file.

DCN and Maximo.xlsx
	B	C	D				H				L	M	N	O	P	Q	R		T	U		W						AC	AD	AE	AF	AG
1	WORKSUBTYPE	TEAM	SUPERVISOR				STATUS				WONUM	WO_CMP_NUM	WO_DCN	SP	DCN_ID	DCN_CMP_NUM	DCN_WO		DCN_TYPE	DCN_STATUS		ASSIGNEE							SUPERVISOR	ASSIGNEEE	WORKSUBTYPE	DCN_TYPEE
5	BUSINESS SUPPORT	DOCS	MCNEILLEYB				CLOSE				5725421	2020-05	16042	DCN-DATA	16042	RFB 8224 / CMP 2020-05	5725421		Engraving	Completed		Ben McNeilley							MENORCAR	Ron Menorca	SRCSD MODIFICATION	CMP - DMG Updates (Not Tracked)
6	BUSINESS SUPPORT	DOCS	OCAMPOG				CLOSE				5713528	2020-05	15983	DCN-DATA	15983	RFB 8224 / CMP 2020-05	5713528		Engraving	Completed		Geronimo Ocampo							ARCILLAR	Ryan Arcilla	BUSINESS SUPPORT	CMP - DMG Updates
7	BUSINESS SUPPORT	DOCS	MALLOS				CLOSE				5707269	2020-195	15998	DCN-DATA	15998	4378	5707269		Engraving	Completed		Sierra Mallo							OCAMPOG	Geronimo Ocampo	BUSINESS SUPPORT	CMP - DMG Updates (Not Tracked)
8	BUSINESS SUPPORT	DOCS	OCAMPOG				CLOSE				5702851	2021-53	15984	DCN-DATA	15984	CMP 2021-53	5702851		Engraving	Completed		Geronimo Ocampo							KIBLERJ	Jeffrey Kibler	BUSINESS SUPPORT	Engraving
10	BUSINESS SUPPORT	DOCS	MALLOS				CLOSE				5697831	2020-195	15946	DCN-DATA	15946	RFB #4378	5697831		Engraving	Completed		Sierra Mallo							MCNEILLEYB	Ben McNeilley	INTERNAL SUPPORT	CMP - DMG Updates (Not Tracked)
16	STANDARD	DOCS	GONZALEZAA				CLOSE				5624022	2020-151	15739	DCN-DATA	15739		5624022		AOP / SOP / Ops Manual	Completed		Genie Flores							JOSEK	Katheryne Jose	PM	CMP - CMMS Updates
17	STANDARD	DOCS	GONZALEZAA				CLOSE				5617654	2020-152	15738	DCN-DATA	15738		5617654		AOP / SOP / Ops Manual	Completed		Genie Flores							RATTUS	Shailza Rattu	BUSINESS SUPPORT	Plotting / Lamination / Labels
18	STANDARD	DOCS	MENORCAR				CLOSE				5613608	2020-141	15634	DCN-DATA	15634		5613608		NTC / CMP / RIK	Completed		Ron Menorca							NAVESJ	Jack Naves
21	STANDARD	DOCS	SINGHH				CLOSE				5604925	2020-148	15608	DCN-DATA	15608		5604925		NTC / CMP / RIK	Completed		Harjeet Singh							AGIUSJ	Jake Agius
22	O&M LIBRARY	DOCS	GONZALEZAA				CLOSE				5598586	2020-05	15601	DCN-DATA	15601		5598586		AOP / SOP / Ops Manual	Completed		Genie Flores
24		DOCS	MCNEILLEYB				CLOSE				5583098	2020-25	15534	DCN-DATA	15534		5583098		NTC / CMP / RIK	Completed		Benjamin McNeilley
25	SRCSD MODIFICATION	DOCS	MCNEILLEYB				CLOSE				5571311	2020-195	15508	DCN-DATA	15508		5571311		NTC / MOC	Completed		Benjamin McNeilley							TRUE		TRUE
26		DOCS	GONZALEZAA				CLOSE				5567307	2020-89	15501	DCN-DATA	15501		5567307		AOP / SOP / Ops Manual	Completed		Genie Flores
27	HC1	DOCS	LACYK				CLOSE				5567222	2020-56	14394	DCN-DATA	14394		5546263		NTC / MOC	Completed		Benjamin McNeilley
28		DOCS	MESSINGJ				CLOSE				5567047	2020-56	15496	DCN-DATA	15496		5567047		NTC / MOC	Completed		Jason Messing
29	HC1	CMMS	MERRILLN				CLOSE				5566791	2020-05	N/A	DCN-DATA
31	O&M LIBRARY	DOCS	GONZALEZAA				CLOSE				5563023	2020-05	15484	DCN-DATA	15484		5563023		AOP / SOP / Ops Manual	Completed		Genie Flores
32	SRCSD MODIFICATION	DOCS	SINGHH				CLOSE				5559378	2020-195	15477	DCN-DATA	15477		5559378		NTC / MOC	Completed		Harjeet Singh
33	O&M LIBRARY	DOCS	GONZALEZAA				CLOSE				5559163	2020-166	15469	DCN-DATA	15469	2020-38	5559163		AOP / SOP / Ops Manual	Completed		Genie Flores
35		DOCS	SINGHH				CLOSE				5549843	2020-56	15445	DCN-DATA	15445		5549843		NTC / MOC	Completed		Harjeet Singh
36		DOCS	MCNEILLEYB				CLOSE				5546263	2020-56	14394	DCN-DATA	14394		5546263		NTC / MOC	Completed		Benjamin McNeilley
37		DOCS	GONZALEZAA				CLOSE				5546203	2020-04	15431	DCN-DATA	15431	MOC 2020-04	5546203		AOP / SOP / Ops Manual	Completed		Genie Flores
38		DOCS	GONZALEZAA				CLOSE				5545832	2020-98	15433	DCN-DATA	15433		5545832		AOP / SOP / Ops Manual	Completed		Genie Flores
39		DOCS	MCNEILLEYB				CLOSE				5545502	2020-92	15420	DCN-DATA	15420		5545502		NTC / AEO / Management Directive	Completed		Benjamin McNeilley
40		DOCS	GONZALEZAA				CLOSE				5545489	2020-04	15422	DCN-DATA	15422	MOC 2020-04	5545489		AOP / SOP / Ops Manual	Completed		Genie Flores
41		DOCS	MCNEILLEYB				CLOSE				5545468	2018-15	15406	DCN-DATA	15406	MOC 2018-15	5545468		NTC / MOC	Completed		Benjamin McNeilley
42		DOCS	MCNEILLEYB				CLOSE				5545461	2020-92	15418	DCN-DATA	15418		5545461		NTC / AEO / Management Directive	Completed		Benjamin McNeilley
43		DOCS	SINGHH				CLOSE				5545361	2018-18	15403	DCN-DATA	15403		5545361		NTC / MOC	Completed		Harjeet Singh
44		DOCS	MCNEILLEYB				CLOSE				5545157	2020-56	15410	DCN-DATA	15410		5545157		NTC / MOC	Completed		Benjamin McNeilley
45		DOCS	MESSINGJ				CLOSE				5541093	2020-04	15413	DCN-DATA	15413	MOC 2020-04	5541093		NTC / MOC	Completed		Jason Messing
46		DOCS	ISACA				CLOSE				5540967	2020-56	14402	DCN-DATA	14402		5540967		NTC / MOC	Completed		Andrei Isac
47		DOCS	MESSINGJ				CLOSE				5540607	2019-15	14397	DCN-DATA	14397	MOC2019-15	5540607		NTC / MOC	Completed		Jason Messing
48		DOCS	SINGHH				CLOSE				5540375	2020-56	14391	DCN-DATA	14391		5540375		NTC / MOC	Completed		Harjeet Singh
49		DOCS	LACYK				CLOSE				5538648	2020-43	14341	DCN-DATA	14341		5532056		NTC / MOC	Completed		Andrei Isac
50		DOCS	GONZALEZAA				CLOSE				5538602	2019-15	14388	DCN-DATA	14388	MOC 2019-15	5538602		AOP / SOP / Ops Manual	Completed		Genie Flores
51		DOCS	GONZALEZAA				CLOSE				5532845	2020-56	14374	DCN-DATA	14374		5532845		AOP / SOP / Ops Manual	Completed		Katheryne Jose
52		DOCS	SINGHH				CLOSE				5523811	2020-56	14348	DCN-DATA	14348		5523811		NTC / MOC	Completed		Harjeet Singh
53		DOCS	GONZALEZAA				CLOSE				5523747	2020-56	14346	DCN-DATA	14346		5523747		AOP / SOP / Ops Manual	Completed		Genie Flores
54		DOCS	MENORCAR				CLOSE				5523742	2020-56	14347	DCN-DATA	14347		5523742		NTC / MOC	Completed		Ron Menorca
55		DOCS	SINGHH				CLOSE				5523372	2020-56	14337	DCN-DATA	14337		5523372		NTC / MOC	Completed		Harjeet Singh
56		DOCS	MENORCAR				CLOSE				5523367	2020-56	14336	DCN-DATA	14336		5523367		NTC / MOC	Completed		Ron Menorca
57		DOCS	MESSINGJ				CLOSE				5523322	2020-56	14335	DCN-DATA	14335		5523322		NTC / MOC	Completed		Jason Messing
58		DOCS	GONZALEZAA				CLOSE				5518612	2020-56	14325	DCN-DATA	14325		5518612		AOP / SOP / Ops Manual	Completed		Genie Flores
59		DOCS	ISACA				CLOSE				5518476	2020-56	14319	DCN-DATA	14319		5518476		NTC / MOC	Completed		Andrei Isac
61		DOCS	MCNEILLEYB				CLOSE				5518441	2020-56	14318	DCN-DATA	14318		5518441		NTC / MOC	Completed		Benjamin McNeilley
WO-DCN

Cell Formulas
Range	Formula
AD25	AD25	=AND($D1<>"",$W1<>"",COUNTIFS($AD$1:$AD$24,$D1,$AE$1:$AE$24,$W1)=0)
AF25	AF25	=AND($B1<>"",$T1<>"",COUNTIFS($AF$1:$AF$24,$B1,$AG$1:$AG$24,$T1)=0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Cell Value	contains "DCN-DATA"	text	NO
N:N,P:P	Expression	=ISERROR(FIND(","&$N1&",",","&SUBSTITUTE($P1," ","")&","" "))	text	NO
M:M,Q:Q	Expression	=ISERROR(FIND(","&$M1&",",","&SUBSTITUTE($Q1," ","")&","" "))	text	NO
L:L,R:R	Expression	=ISERROR(FIND(","&$L1&",",","&SUBSTITUTE($R1," ","")&","" "))	text	NO
D:D,W:W	Expression	=AND($D1<>"",$W1<>"",COUNTIFS($AD$1:$AD$24,$D1,$AE$1:$AE$24,$W1)=0)	text	NO
B:B,T:T	Expression	=AND($B1<>"",$T1<>"",COUNTIFS($AF$1:$AF$24,$B1,$AG$1:$AG$24,$T1)=0)	text	NO
H:H,U:U	Expression	=AND($H1<>"",$U1<>"",COUNTIFS($AH$1:$AH$24,$H1,$AI$1:$AI$24,$U1)=0)	text	NO

 

[Peter_SSs]

I also sent you a pm (start a conversation) that have the link to the actual excel file.

If you want to share a link to an actual file then you need to post it publicly in your thread (see #4 of the Forum Rules) though you should also note that many members are unwilling, or unable due to workplace security settings, to download such files.

Based on what you have shown above, see if this does what you want

burniksapwet.xlsm
	M	N	O	P	Q
1
2	2020-05				ABC 2020-05
3	2020-195				2020-19
4	2020-25				2020-25
5
6	2019				ABC 2019-15
7	2019-15				ab cd/ef2019-15
Compare (6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M7,Q2:Q7	Expression	=AND($M2<>"",$M2=RIGHT($Q2,LEN($M2)))	text	NO

 

[burniksapwet]

If you want to share a link to an actual file then you need to post it publicly in your thread (see #4 of the Forum Rules) though you should also note that many members are unwilling, or unable due to workplace security settings, to download such files.

So sorry for this. Still being a forum noob I guess. Will read that for sure. Thank you for the heads up.

Based on what you have shown above, see if this does what you want

How do you do the opposite? Trying to see which ones don't match so that I can examine the inconsistencies with the data. Thank you so much.
Here is a sample of what I am trying the code to achieve

DCN and Maximo.xlsx
	M	N	O	P	Q
1	test1				test2
2	2020-05				ABC 2020-05 ABC
3	2020-195				2020-19
4	2020-25				MOC 2020-25 MOC 2020-26
5	2020-05				2 0 2 0 - 0 5
6	2019-15				ABC2019 - 15
7	2019-15				ab cd/ef2019-15
Sheet1

 

[Peter_SSs]

Just change the = in the formula to not equals <>

 

[burniksapwet]

Code is working perfectly. I do just need one more help for that code. Seems like the code doesnt like it when M field is blank as it is not being highlighted even though there is information in Q. Thank you.

DCN and Maximo.xlsx
	M	N	O	P	Q
1	WO_CMP_NUM	WO_DCN	SP	DCN_ID	DCN_CMP_NUM
7		10722	DCN-DATA	10722	2016-33
9		10722	DCN-DATA	10722	2016-33
10		10722	DCN-DATA	10722	2016-33
12		10338	DCN-DATA	10338	2016-33
19		15486	DCN-DATA	15486	2020-05
40		15577	DCN-DATA	15577	2020-119
51		13946	DCN-DATA	13946	2020-12
WO-DCN

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O:O	Cell Value	contains "DCN-DATA"	text	NO
N:N,P:P	Expression	=ISERROR(FIND(","&$N1&",",","&SUBSTITUTE($P1," ","")&","" "))	text	NO
M:M,Q:Q	Expression	=AND($M1<>"",$M1<>RIGHT($Q1,LEN($M1)))	text	NO

 

[Peter_SSs]

Seems like the code doesnt like it when M field is blank

Hard to keep up with the changes!
Previously you told us, on more than one occasion:

Would like to not include blank fields once again.

=$M2<>RIGHT($Q2,MAX(LEN($M2),1))

 

[burniksapwet]

Hard to keep up with the changes!
Previously you told us, on more than one occasion:

=$M2<>RIGHT($Q2,MAX(LEN($M2),1))

Thank you so much for all the help that you have provided. This has been so much useful to us. More power to you and more power to this forums. Thank you everyone.

 

[Peter_SSs]

Cheers.