Conditional formatting comparing multiple arrays

[paulgunther]

I have a spreadsheet showing different layouts for a golf course. Each row of 18 columns has two possible Pin placements (blue or gold). With 262,144 possible layouts, I would like to not list duplicates. This means I have to compare all 18 cells in a row to every other row of 18 looking for an exact match and flag it via formatting.

Thanks in advance!

 

[etaf]

MUST be a better way as you have 365 - to do all in 1 function
but i have a helper to concatenate all 18 holes together and then look for duplicates , ie the count is 2 or more
but i played with a couple of idea and so far not solved - strange, should be easy with 365 version

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Hole-1	Hole-2	Hole-3	Hole-4	Hole-5	Hole-6	Hole-7	Hole-8	Hole-9	Hole-10	Hole-11	Hole-12	Hole-13	Hole-14	Hole-15	Hole-16	Hole-17	Hole-18	Duplicate
2		gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	gold	goldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold	2
3
4
5
6		gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	gold	goldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold	2
7
8
9		gold	gold	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	blue	gold	blue	gold	goldgoldbluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold	1
10
11
Sheet1

Cell Formulas
Range	Formula
T2:T11	T2	=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2
U2:U11	U2	=IF(B2="","",COUNTIF($T$2:$T$25,T2))

 

[paulgunther]

MUST be a better way as you have 365 - to do all in 1 function
but i have a helper to concatenate all 18 holes together and then look for duplicates , ie the count is 2 or more
but i played with a couple of idea and so far not solved - strange, should be easy with 365 version

Thanks for the fast reply!
I thought this would be easy as well. Every time I say "oh, I'll just..." I hit a roadblock.
This forum has always found a way with my silly challenges. I( hope this one is no different.
Cheers!

 

[etaf]

well searching google

Match each row against all other rows - microsoft excel

the only solution i found is exactly as I have done - using concatenate and then using conditional formatting to highlight the match , rather than a column showing the duplicate