Highlighting duplicates between 2 sheets that contain different orderings

[crtk09]

Hey all! Any help with this problem I'm bamboozling myself with would be hugely appreciated!

So I have two different sheets I'm looking to compare and highlight duplicates of:
Sheet 1 - Contains all current employee names in a "firstname*space*surname" format. It also contains a " [C]" to indicate Contractors. So, "firstname*space*surname*space*[C]" or vice versa without the "C".
Sheet 2 - Contains a list of employees that have only recorded their time spent working. This list, however, names are formatted between either "firstname*space*surname", "surname.firstname" and "surname*comma*firstname". A very random mix of orderings to work with.

So my question is, I would like to highlight the employees on sheet 1 that are found on sheet 2 (duplicates), however, I'm really struggling to come up with something between conditional formatting and VBA to accomplish this.
Is something like this even possible by asking Excel to essentially search those different variations, ensuring it doesn't "break out" of that naming sequence that sheet 2 contains? (if that makes any sense, lol)

Thank you!

 

[crtk09]

Random thought that might help this process:
Sheet 2 - Could we somehow tell Excel to somehow reorder those names I mentioned, so that they are corresponding with the ones on sheet 1? Or, find and replace (i.e. delete) the commas, then reorder, if possible.

 

[Peter_SSs]

Welcome to the MrExcel board!

In future, it would help if you can give us some dummy sample data in a form that we can copy/paste to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

This sort of text question is often a bit trick, especially if you have multi-word names lime Vincent van Gogh etc. However, see if this Conditional Formatting helps. I have done it on a single sheet but you should be able to adapt to two sheets.

21 12 18.xlsm
	A	B	C
1
2	Tom Jones		Ken Smith
3	Tim Timms		Hall.Kim
4	Kim Hall [C]		Jones.Tom
5	Ken Smith		Baines.Bob
6	Ted Baxter		Derf,Fred
7	Fred Derf
8
CF Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNT(SEARCH(" "&TRIM(SUBSTITUTE(A2,"[C]",""))&" "," "&SUBSTITUTE(SUBSTITUTE(C$2:C$6&" "&C$2:C$6,"."," "),","," ")&" "))	text	NO

 

[crtk09]

Welcome to the MrExcel board!

In future, it would help if you can give us some dummy sample data in a form that we can copy/paste to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

This sort of text question is often a bit trick, especially if you have multi-word names lime Vincent van Gogh etc. However, see if this Conditional Formatting helps. I have done it on a single sheet but you should be able to adapt to two sheets.

21 12 18.xlsm
	A	B	C
1
2	Tom Jones		Ken Smith
3	Tim Timms		Hall.Kim
4	Kim Hall [C]		Jones.Tom
5	Ken Smith		Baines.Bob
6	Ted Baxter		Derf,Fred
7	Fred Derf
8
CF Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNT(SEARCH(" "&TRIM(SUBSTITUTE(A2,"[C]",""))&" "," "&SUBSTITUTE(SUBSTITUTE(C$2:C$6&" "&C$2:C$6,"."," "),","," ")&" "))	text	NO

Hey Peter,

Thank you so much for your reply! My apologies for not including any kind of example / test data, you'll find that below:

Sheet 1:

Copy of Directory_by_Organization_BD_RM_combined_9Dec2021-v2.1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Worker (Workday)	Supervisory Organization	Phone	Email Address	Business Title	Location	Management Chain	Department #	Department Name	Worker Type	Company
2	Aaron Smith	SUP5.1 Mary Smith	123 456 789	test@email.com		UK - London	James Smith	20	R&D	FTE	Test Company
3	Aashish Rush [C]	SUP5.1 Mary Smith	124 456 789	test@email.com		India Remote	James Smith	20	R&D	Contractor	Test Company
4	Aashutosh Polo [C]	SUP5.1 Mary Smith	125 456 789	test@email.com		India Remote	James Smith	20	R&D	Contractor	Test Company
5	Muhammad Chavan [C]	SUP5.1 Mary Smith	126 456 789	test@email.com		India Remote	James Smith	20	R&D	Contractor	Test Company
6	Abhishek Smith [C]	Sup6.5 Tom Jenkins	127 456 789	test@email.com		India Remote	James Smith	20	Sales	Contractor	Test Company
7	Adnan Adnanson	Sup6.5 Tom Jenkins	128 456 789	test@email.com		US Remote - Colorado	James Smith	20	R&D	FTE	Test Company
8	Adriana Michaelson	Sup9.5 Mike Toms	129 456 789	test@email.com		Toronto, Canada	James Smith	20	R&D	FTE	Test Company
9	Afsha Ipohm [C]	Sup9.5 Mike Toms	130 456 789	test@email.com		India Remote	James Smith	20	Marketing	Contractor	Test Company
10	Afshan Pmiz [C]	Sup9.5 Mike Toms	131 456 789	test@email.com		India Remote	James Smith	20	R&D	Contractor	Test Company
11	Aishwarya Bulsamic [C]	Sup9.5 Mike Toms	132 456 789	test@email.com		India Remote	James Smith	20	R&D	Contractor	Test Company
12	Rahul Smith	Sup9.5 Mike Toms	132 456 789	test@email.com		India Remote	James Smith	20	R&D	FTE	Test Company
13	Miriam Jones	Sup9.5 Mike Toms	132 456 789	test@email.com		India Remote	James Smith	20	R&D	FTE	Test Company
Sheet1

Cell Formulas
Range	Formula
J2:J13	J2	=IF(ISNUMBER(SEARCH("[C]",A2)),"Contractor","FTE")

Sheet 2 - The "problem" sheet, per se:

Copy of Directory_by_Organization_BD_RM_combined_9Dec2021-v2.1.xlsm
	A	B	C	D	E	F	G	H
1	Author	Summary	Work description	Issue	Project	Started	Time spent seconds	Time spent
2	Aaron Smith	Fixed issue	Software operational	XD-123	Super Server V1	2021-12-17T15:39:31.092+0000	600	10m
3	Polo, Aashutosh	Fixed issue	Bugs squashed	XD-124	Super Server V2	2021-12-16T15:12:09.249+0000	1800	30m
4	Muhammad Chavan	Fixed issue	Server operational	XD-125	Super Server V3	2021-12-15T14:51:00.000+0000	300	5m
5	Abhishek Smith	Fixed issue	Software operational	XD-126	Super Server V4	2021-12-15T14:09:18.892+0000	600	10m
6	Adnanson, Adnan	Fixed issue	Bugs squashed	XD-127	Super Server V5	2021-12-17T15:39:31.092+0001	450	10m
7	Michaelson, Adriana	Fixed issue	Server operational	XD-128	Super Server V6	2021-12-16T15:12:09.249+0001	300	30m
8	Afsha Ipohm	Fixed issue	Software operational	XD-129	Super Server V7	2021-12-15T14:51:00.000+0001	150	5m
9	Afshan Pmiz	Fixed issue	Bugs squashed	XD-130	Super Server V8	2021-12-15T14:09:18.892+0001	200	10m
10	Aishwarya Bulsamic	Fixed issue	Server operational	XD-131	Super Server V9	2021-12-17T15:39:31.092+0002	300	10m
11	Rush, Aashish	Fixed issue	Software operational	XD-132	Super Server V10	2021-12-16T15:12:09.249+0002	500	30m
12	Rahul Michael Smith	Fixed issue	Bugs squashed	XD-133	Super Server V11	2021-12-15T14:51:00.000+0002	450	5m
13	Jones, Miriam Williams	Fixed issue	Server operational	XD-134	Super Server V12	2021-12-15T14:09:18.892+0002	600	10m
Sheet2

Hope this gives anyone a good idea of what we're dealing with here! Quite a finicky mess of naming combinations across these two sheets.

 

[crtk09]

Welcome to the MrExcel board!

In future, it would help if you can give us some dummy sample data in a form that we can copy/paste to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

This sort of text question is often a bit trick, especially if you have multi-word names lime Vincent van Gogh etc. However, see if this Conditional Formatting helps. I have done it on a single sheet but you should be able to adapt to two sheets.

21 12 18.xlsm
	A	B	C
1
2	Tom Jones		Ken Smith
3	Tim Timms		Hall.Kim
4	Kim Hall [C]		Jones.Tom
5	Ken Smith		Baines.Bob
6	Ted Baxter		Derf,Fred
7	Fred Derf
8
CF Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNT(SEARCH(" "&TRIM(SUBSTITUTE(A2,"[C]",""))&" "," "&SUBSTITUTE(SUBSTITUTE(C$2:C$6&" "&C$2:C$6,"."," "),","," ")&" "))	text	NO

Hi again, Peter,

Just wanted to follow up and say after some testing, the formula you provided with your response actually seems to be working for me!! I've got the two list names side by side currently and it appears to be highlighted as it should be, so thank you!

Now my question is, how do I re-work the formula so that it communicates between my two sheets? I haven't done much cross-sheet-conditional formatting before...

 

[Peter_SSs]

how do I re-work the formula so that it communicates between my two sheets?

You will need to adjust for your sheet names and ranges, but the gist of it is this.

crtk09.xlsm
	A
1
2	Ken Smith
3	Hall.Kim
4	Jones.Tom
5	Baines.Bob
6	Derf,Fred
7
Sheet5

crtk09.xlsm
	A
1
2	Tom Jones
3	Tim Timms
4	Kim Hall [C]
5	Ken Smith
6	Ted Baxter
7	Fred Derf
8
CF Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNT(SEARCH(" "&TRIM(SUBSTITUTE(A2,"[C]",""))&" "," "&SUBSTITUTE(SUBSTITUTE(Sheet5!A$2:A$6&" "&Sheet5!A$2:A$6,"."," "),","," ")&" "))	text	NO

Sheet 2 - Contains a list of employees that have only recorded their time spent working. This list, however, names are formatted between either "firstname*space*surname", "surname.firstname" and "surname*comma*firstname".

I note that with your samples provided in post #4 there are even more variations than you have listed here.
Therefore my formula will not match some names that probably should be matched (eg Adnan Adnanson)

 

[crtk09]

You will need to adjust for your sheet names and ranges, but the gist of it is this.

crtk09.xlsm
	A
1
2	Ken Smith
3	Hall.Kim
4	Jones.Tom
5	Baines.Bob
6	Derf,Fred
7
Sheet5

crtk09.xlsm
	A
1
2	Tom Jones
3	Tim Timms
4	Kim Hall [C]
5	Ken Smith
6	Ted Baxter
7	Fred Derf
8
CF Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=COUNT(SEARCH(" "&TRIM(SUBSTITUTE(A2,"[C]",""))&" "," "&SUBSTITUTE(SUBSTITUTE(Sheet5!A$2:A$6&" "&Sheet5!A$2:A$6,"."," "),","," ")&" "))	text	NO

I note that with your samples provided in post #4 there are even more variations than you have listed here.
Therefore my formula will not match some names that probably should be matched (eg Adnan Adnanson)

Oh, haha! Those were total dummy names I made up on the spot at the confidentiality of the employees actually listed on these sheets. Probably could've come up with a more creative one than "Adnan Adnanson", lol.

Anyway, thank you so much again for the revised formula! I implemented it earlier and have been testing the highlighted and non-highlighted names against the data it's comparing to, and so far I've got a 100% accuracy rate! I'm going to continue working my way through just to be sure, or until I lose my mind manually checking 500 names, but so far, so good!

 

[Peter_SSs]

so far I've got a 100% accuracy rate!

Good news.
? Fingers crossed for the remaining checks.