Conditional formatting comparing multiple arrays

paulgunther

New Member
Joined
Oct 30, 2019
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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!
 
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
ABCDEFGHIJKLMNOPQRSTU
1Hole-1Hole-2Hole-3Hole-4Hole-5Hole-6Hole-7Hole-8Hole-9Hole-10Hole-11Hole-12Hole-13Hole-14Hole-15Hole-16Hole-17Hole-18Duplicate
2goldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegoldgoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold2
3  
4  
5  
6goldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegoldgoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold2
7  
8  
9goldgoldbluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegoldgoldgoldbluegoldbluebluegoldbluebluegoldbluebluegoldbluebluegoldbluegold1
10  
11  
Sheet1
Cell Formulas
RangeFormula
T2:T11T2=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2
U2:U11U2=IF(B2="","",COUNTIF($T$2:$T$25,T2))
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top