Excel 2010 Issue

S

sarobertson1

New Member
Joined
Sep 11, 2013
Messages
2
Hi all,

I have an excel issue that i could really do with some help on, it may actually be relatively simple but having spent time searching through the internet i still cant work it out. Basically i have a 100 multiple choice tests with 5 questions and i am trying to see if there are exact copies anywhere in the data set. for instance;
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Questions[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[TD]Test4[/TD]
[TD]Test5[/TD]
[TD]Test6[/TD]
[TD]Test7[/TD]
[TD]Test8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Q1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Q2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Q3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Q4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Q5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Each Column represents a test sheet that has been completed, each row shows the answers to each individual question. What i want to do is have Excel identify duplication, in the table Test 1 and Test 4 have been completed in exactly the same. How do i identify this with an excel spreadsheet, ideally highlighting instances of duplication through conditional formatting?

any help on this will be greatly appreciated.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi Patel, the desired result is that there is an indication or a highlight of any duplicate columns. I would like in the table above for instance, Test 1 and Test 4 numbers to be highlighted as duplicates, if there were any other duplicates throughout the other tests i would also like them to be highlighted. does that help?
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 265"]
<tbody>[TR]
[TD="class: xl63, width: 55, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Questions
[/TD]
[TD="class: xl64, bgcolor: #92D050"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test3
[/TD]
[TD="class: xl64, bgcolor: #92D050"]Test4
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test5
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test6
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test7
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test8
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Q1
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Q2
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Q3
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Q4
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Q5
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl64, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]


Formula (CF):

Code: 
=SUMPRODUCT(-(B$3=$B$3:$I$3),-(B$4=$B$4:$I$4),-(B$5=$B$5:$I$5),-(B$6=$B$6:$I$6),--(B$7=$B$7:$I$7))>1

Or

=COUNTIFS($B$3:$I$3,B$3,$B$4:$I$4,B$4,$B$5:$I$5,B$5,$B$6:$I$6,B$6,$B$7:$I$7,B$7)>1

Markmzz
 
Last edited:
Upvote 0
Markmzz, The OP describes that there are 100 questions. Perhaps you didn't catch that, but I believe your CF formula suggestions that could handle 5 questions would be problematic for 100 questions.

Perhaps a VBA approach is a better fit. The macro shown below will make a unique identifier for each set of answers.
eg: Test1="T-1". If another Test has the same answers, it will also be labeled "T-1".
The labels can be placed in Row 1 and a simple CF rule could then be used to highlight columns with duplicate labels.
Excel Workbook
ABCDEFGHI
1T-1T-2T-3T-1T-5T-3T-7T-8
2QuestionsTest1Test2Test3Test4Test5Test6Test7Test8
3Q112313323
4Q221221222
5Q323123123
6Q431331313
7Q511312333
8Q611312333
Sheet


Code: 
Sub CopyCat()
'--compares concatenated strings and labels each unique pattern
'   add Reference to Microsoft Scripting Runtime 

    Dim v As Variant, vResults As Variant
    Dim i As Long
    Dim dic As Scripting.Dictionary
    Dim rData As Range

    
    Set rData = Range("B3:I102") 'edit to match your range
    v = rData.Value
    ReDim vResults(1 To UBound(v, 2))

    
    '--concatenate values into a string for each column
    For i = 1 To UBound(v, 2)
        With Application
            vResults(i) = "X" & Join$( _
                .Transpose(.Index(v, 0, i)), "")
        End With
    Next i

    
    Set dic = CreateObject("Scripting.Dictionary")

    
    '--use dictionary to mark unique strings
    For i = 1 To UBound(vResults)
        If Not dic.Exists(vResults(i)) Then
            dic.Add vResults(i), i
            vResults(i) = "T-" & i
        Else
            vResults(i) = "T-" & dic(vResults(i))
        End If 
    Next i

    
    '--display results
    rData(-1, 1).Resize(1, rData.Columns.Count).Value = vResults
End Sub
 
Upvote 0
JS411 said:
Markmzz, The OP describes that there are 100 questions. Perhaps you didn't catch that, but I believe your CF formula suggestions that could handle 5 questions would be problematic for 100 questions.
Click to expand...

sarobertson1 said:
Hi all,

I have an excel issue that i could really do with some help on, it may actually be relatively simple but having spent time searching through the internet i still cant work it out. Basically i have a 100 multiple choice tests with 5 questions and i am trying to see if there are exact copies anywhere in the data set. for instance; Each Column represents a test sheet that has been completed, each row shows the answers to each individual question. What i want to do is have Excel identify duplication, in the table Test 1 and Test 4 have been completed in exactly the same. How do i identify this with an excel spreadsheet, ideally highlighting instances of duplication through conditional formatting?

any help on this will be greatly appreciated.
Click to expand...

Hi JS411,

I think that you are wrong. Look at the text above in red.

Markmzz
 
Upvote 0
markmzz said:
Hi JS411,

I think that you are wrong. Look at the text above in red.

Markmzz
Click to expand...

Oops ...my mistake in misreading the OP.

The macro will still work as an alternative; and perhaps could be of use for similar problems with more rows of questions.

Thanks Markmzz
 
Upvote 0
Here is a sample with 100 tests:

[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl67, width: 27, bgcolor: #D9D9D9"][/TD]
[TD="class: xl68, width: 55, bgcolor: #D9D9D9"]A
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]B
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]C
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]D
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]E
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]F
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]G
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]H
[/TD]
[TD="class: xl68, width: 37, bgcolor: #D9D9D9"]I
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CP
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CQ
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CR
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CS
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CT
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CU
[/TD]
[TD="class: xl68, width: 39, bgcolor: #D9D9D9"]CV
[/TD]
[TD="class: xl68, width: 45, bgcolor: #D9D9D9"]CW
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Questions
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test3
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test4
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test5
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test6
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test7
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test8
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test93
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test94
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test95
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test96
[/TD]
[TD="class: xl66, bgcolor: #92D050"]Test97
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test98
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test99
[/TD]
[TD="class: xl65, bgcolor: transparent"]Test100
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Q1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Q2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]Q3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]Q4
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #D9D9D9"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]Q5
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: #92D050, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:

Code: 
=COUNTIFS($B$3:$CW$3,B$3,$B$4:$CW$4,B$4,$B$5:$CW$5,B$5,$B$6:$CW$6,B$6,$B$7:$CW$7,B$7)>1

Or

=SUMPRODUCT((B$3=$B$3:$CW$3)*(B$4=$B$4:$CW$4)*(B$5=$B$5:$CW$5)*(B$6=$B$6:$CW$6)*(B$7=$B$7:$CW$7))>1

No problem here.


Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Exact Match with ISNUMBER(SEARCH)
Replies
2
Views
202
danhenshy23
D
G
  • Question Question
Find Duplicate Values using a Criteria
Replies
2
Views
171
Eric W
Eric W
K
  • Question Question
Return column header name for item located in an array
Replies
4
Views
190
DanteAmor
DanteAmor
trishcollins
  • Question Question
Find and filter on two different unique values
Replies
5
Views
212
trishcollins
trishcollins
K
  • Solved
Conditionally Format cells based on relationship to a max value
Replies
1
Views
33
Fluff
Fluff

Forum statistics

Threads
1,223,648
Messages
6,173,551
Members
452,520
Latest member
Pingaware

Share this page

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