Matrix Returning

C

CascadeHope

New Member
Joined
Jun 26, 2014
Messages
7
Hello everyone,

I've been working on a project for work, and am needing a little help for it..

I have a matrix of values, and below it is a section where you enter in your own values, and see if they exist anywhere in the matrix, I want it to return any conflicts in the the matrix.
I'm very bad at putting this into words, so hopefully this quick example helps;

A
|__ |__B__C__D__E
A|x_|__|___|__|___|
B|__|_x|___|_n|___|
C|__|__|_x_|__|___|
D|__|_n_|___|x_|___|
E|__|__|___|__|_x_|

In this quick example, The middle section is X'd out, since there are no conflicts between the same value, and B>>D s a conflict, so they are indicated by an n.

The matrix will not change, so this part works fine.

Below it, if you enter an A and an E for example, it will highlight the rows and columns of A and E, to make it more clear to see if any conflicts occur, which none do.

What I would like added, is if there is a conflict found, it will give me the row and column that the highlighted row and column meet.

Sorry if this still isn't very clear...
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Can you give us an example of what your matrix looks like, what values you might enter below the matrix, and explain what "conflicts" would arise from that configuration.
 
Upvote 0
I gave a quick example of what the matrix looks like above. Some example values would be one cell containing Maintenance Technician, while another contains Maintenance Superuser. These are conflicts since no Superuser should be a technician.

What is entered below is the same values in the matrix.
 
Upvote 0
Can you please expand on this ...

Are both "Maintenance Technician" and "Maintenance Superuser" in the matrix? If so, how is there a conflict? Do we need to know various external rules, e.g. no superuser can be a technician?

Or is one value in the matrix and one value below the matrix?

Also, what do you mean by "What is entered below is the same values in the matrix"?

It would really help if you could show us a complete example: what is in the matrix, what is entered below the matrix, and what conflicts occur and why?
 
Upvote 0
I can't give all of the information, but the conflicts will always be marked with a 'n' in the cell that is a conflict.

When I say that the cells below will contain the same as the cells, I mean that the first row and column values (in the above example, A through E), will be what is entered below aswell.

So if Apple was one of the matrix values, I would enter Apple in the below formula section, and both the row and column that contain the contents of 'Apple' would be highlighted blue.

hopefully this explains it more.
 
Upvote 0
So in essence, all you're looking to do is highlight in blue a particular row/column specified by the user?

Perhaps something like:

Excel 2010
ABCDEF
Highlight: row, and

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Blah[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Blah[/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Apple[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"]N[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #FFFF00, align: center"]Apple[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]Orange[/TD]
[TD="align: center"]column[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

where B2:F6 is conditionally formatted using the formula:
=OR($A2=$B$8,B$1=$B$9)

Or, if you want to specify just "Apple" and have the Apple row and column highlighted:
=OR($A2=$B$8,B$1=$B$8)

Is this closer to what you're looking for?
 
Upvote 0
This is pretty much what I'm looking for, yes.

For the below section, if it were:

Highlight: Apple
Orange

As listed in your guessed example, it would highlight both Apple and Orange. (both the row and column would contain the same data.) ((Not sure how you put an excel sheet on here, but I have copied yours and edited it to try and show more of what is expected))


[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Blah[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Blah[/TD]
[TD="bgcolor: #808080, align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"] n[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Apple[/TD]
[TD="bgcolor: #538DD5, align: center"]n[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"]x[/TD]
[TD="bgcolor: #538DD5, align: center"]n[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] n[/TD]
[TD="bgcolor: #538DD5, align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Blah[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]
[TD="bgcolor: #808080, align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Highlight:[/TD]
[TD="bgcolor: #FFFF00, align: center"]Apple[/TD]
[TD]row, and[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]Orange[/TD]
[TD="align: center"]column[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
As you can see, Apple and Orange in both the row and column have been highlighted. (Using: =OR(ISNUMBER(MATCH(B$2,$B$85:$B$200,FALSE)),ISNUMBER(MATCH($B2,$B$85:$B$200,FALSE))) as a conditional formatting)

What I would like to have happen now though, is if any of the cells that are highlighted contain an 'n', if the highlighting overlaps (For example, D5 and E4), it would return something like, APPLE|ORANGE

Edit: Can't get the coloring of my cells to stay, but I'm hoping I made it more clear still.
 
Upvote 0
I tend to switch to VBA when formulae approaches start getting complicated. If VBA is OK for you, then something like this perhaps:

Excel 2010
ABCDEF
List Conflicts here:
Row:Pear | Col:Apple
Row:Pear | Col:Peach
Row:Banana | Col:Orange
Row:Orange | Col:Banana

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pear[/TD]
[TD="align: center"]Banana[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Peach[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Pear[/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Banana[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Peach[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1
​



Formula in B9:B13 (array-entered): {=ShowOccurrences(A1:F6,"N")}

Code: 
Function ShowOccurrences(rng As Range, sToFind As String) As String()

    'Function expects rng to have a header row and a header column, returns matches as single column
    Dim rngCell As Range
    Dim sTemp() As String
    Dim lCount As Long, lRows As Long
    
    lRows = Application.Caller.Rows.Count
    ReDim sTemp(1 To lRows, 1 To Application.Caller.Columns.Count)
    
    For Each rngCell In rng.Offset(1, 1).Resize(rng.Rows.Count - 1, rng.Columns.Count - 1)
        If rngCell.Value = sToFind Then
            lCount = lCount + 1
            If lCount > lRows Then GoTo EndFunction
            sTemp(lCount, 1) = "Row:" & rng.Columns(1).Cells(rngCell.Row) & " | Col:" & rng.Rows(1).Cells(rngCell.Column)
        End If
    Next rngCell

EndFunction:
    ShowOccurrences = sTemp
    
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

trishcollins
  • Solved
Excel formula to return the formula from a table using the table header as a reference
2
Replies
17
Views
696
trishcollins
trishcollins
K
  • Question Question
Stocktake of Barcoded Inventory in Excel using a Barcode Scanner
Replies
2
Views
127
Kobus Horn
K
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
102
MARK858
MARK858
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
127
Fluff
Fluff

Forum statistics

Threads
1,223,920
Messages
6,175,373
Members
452,638
Latest member
Oluwabukunmi

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