VBA Help for an Excel Matrix

B

Burak

New Member
Joined
Oct 28, 2017
Messages
3
Hello Everyone,

I've just joined this group and I have a simple question for you that I swamped while coding it :)

First of all, I have a dummy TEST and PARAMETER table in my first sheet. Which is like;

[TABLE="class: grid, width: 891, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Parameter 1[/TD]
[TD]Parameter 2[/TD]
[TD]Parameter 3[/TD]
[TD]Parameter 4[/TD]
[TD]Parameter 5[/TD]
[TD]Parameter 6[/TD]
[TD]Parameter 7[/TD]
[TD]Parameter 8[/TD]
[TD]Parameter 9[/TD]
[TD]Parameter 10[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 4[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 6[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 7[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Test 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 10[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do with this table is; user will select the parameter(s) and another sheet will display related tests for each parameter.

For example, user selects parameter 1 and parameter 3. Then, I want to create a report sheet like;

[TABLE="class: grid, width: 82, align: center"]
<tbody>[TR]
[TD]Parameter 1[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[/TR]
[TR]
[TD]Test 6[/TD]
[/TR]
[TR]
[TD]Parameter 3[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[/TR]
[TR]
[TD]Test 5[/TD]
[/TR]
[TR]
[TD]Test 7[/TD]
[/TR]
</tbody>[/TABLE]

I really couldn't figure out the VBA code configuration for it. I believe, there is a simple way, but I'm trying.
Therefore, I wonder your thoughts and I'd be glad if you help.

Thank you.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
I created a UserForm. On the UserForm I added 1 Button. For the UserForm_Activate code I have

Code: 
Private Sub UserForm_Activate()
Dim r As Range
Dim ar()
Set r = Range("B1", Range("B1").End(xlToRight))
ar = r.Value
For i = 1 To UBound(ar, 2)
    Me.ListBox1.AddItem ar(1, i)
Next i
Me.ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

Then, the code for the Button_Click Event I have

Code: 
Private Sub CommandButton1_Click()
Dim SD As New Dictionary
Dim Matrix()
Dim ws As Worksheet
Dim Res()
Dim Cnt As Long
Dim r As Range
Cnt = 0
Set ws = Sheets.Add(after:=Sheets(ActiveWorkbook.Sheets.Count))
ws.Name = "Results"
Matrix = Sheets("Sheet1").Range("A1").CurrentRegion.Value
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        SD.Add Me.ListBox1.List(i), Me.ListBox1.List(i)
    End If
Next i
For i = 2 To UBound(Matrix, 2)
    If SD.Exists(Matrix(1, i)) Then
        Cnt = Cnt + 1
        ReDim Preserve Res(1 To Cnt)
        Res(Cnt) = Matrix(1, i)
        For j = 2 To UBound(Matrix)
            If Matrix(i, j) = "X" Then
                Cnt = Cnt + 1
                ReDim Preserve Res(1 To Cnt)
                Res(Cnt) = Matrix(j + 1, 1)
            End If
        Next j
    End If
Next i
Set r = ws.Range("A1").Resize(UBound(Res), 1)
r.Value = Application.Transpose(Res())

End Sub

When you open the userform, the user will select which Parameters they want. Then they click the button. The code will create a new sheet called "Results" and output the information the way you said in your original post.

This code uses a Dictionary, so you will have to add a reference to Microsoft Scripting Runtime. Tools-->References-->Microsoft Scripting Runtime.

Let me know if you have any questions.
 
Upvote 0
I made a small correction to the code above.

Code: 
Private Sub CommandButton1_Click()
Dim SD As New Dictionary
Dim Matrix()
Dim ws As Worksheet
Dim Res()
Dim Cnt As Long
Dim r As Range
Cnt = 0
Set ws = Sheets.Add(after:=Sheets(ActiveWorkbook.Sheets.Count))
ws.Name = "Results"
Matrix = Sheets("Sheet1").Range("A1").CurrentRegion.Value
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        SD.Add Me.ListBox1.List(i), Me.ListBox1.List(i)
    End If
Next i
For i = 2 To UBound(Matrix, 2)
    If SD.Exists(Matrix(1, i)) Then
        Cnt = Cnt + 1
        ReDim Preserve Res(1 To Cnt)
        Res(Cnt) = Matrix(1, i)
        For j = 2 To UBound(Matrix)
            If Matrix(j, i) = "X" Then
                Cnt = Cnt + 1
                ReDim Preserve Res(1 To Cnt)
                Res(Cnt) = Matrix(j, 1)
            End If
        Next j
    End If
Next i
Set r = ws.Range("A1").Resize(UBound(Res), 1)
r.Value = Application.Transpose(Res())
Unload Me
End Sub
Private Sub UserForm_Activate()
Dim r As Range
Dim ar()
Set r = Range("B1", Range("B1").End(xlToRight))
ar = r.Value
For i = 1 To UBound(ar, 2)
    Me.ListBox1.AddItem ar(1, i)
Next i

Me.ListBox1.MultiSelect = fmMultiSelectExtended
End Sub
Sub RunIt()
UserForm1.Show False
End Sub
 
Upvote 0
Dear @lrobbo314 ,

Thanks for your help and diligent work. However, I got error on line "Dim SD As New Dictionary" and it says "User-defined type not defined" I couldn't understand what causes it.

On the other hand, can't this be done w/o using userform? Do you have any idea?

Thank you.
 
Upvote 0
Did you add the reference to Microsoft Scripting Runtime?

I did come up with a formula solution.

I pasted your data in range A2:K12.
To simulate the user 'selecting' a parameter, I add a '1' to the cell above the parameter in row 1. So to select parameter 1 , the user would put a 1 in B1. Parameter 5 would be F1.

Then, in M2 I put this formula.

Code: 
=IFERROR(INDEX($B$2:$K$2,MATCH(SMALL(IF(TRANSPOSE($B$1:$K$1=1),ROW(INDIRECT("1:10"))),COLUMN(A1)),TRANSPOSE($B$1:$K$1=1)*ROW(INDIRECT("1:10")),0)),"")

Hit Ctrl+Shift+Enter because it is an array formula. Copy it across to V2.

Then in M3 I put this formula.

Code: 
=IFERROR(INDEX($A$3:$A$12,MATCH(SMALL(IF(INDIRECT(ADDRESS(3,MATCH(M$2,$B$2:$K$2,0)+1)&":"&ADDRESS(12,MATCH(M$2,$B$2:$K$2,0)+1))="X",ROW(INDIRECT("1:10"))),ROW($A1)),(INDIRECT(ADDRESS(3,MATCH(M$2,$B$2:$K$2,0)+1)&":"&ADDRESS(12,MATCH(M$2,$B$2:$K$2,0)+1))="X")*(ROW(INDIRECT("1:10"))),0)),"")

Ctrl+Shift+Enter again. Copy across to V3 then copy from M3:V3 down to M12:V12.

Not sure if this formula approach is better for you.

If you're not using a userform then how would you like to capture which parameters the user wants to select?
 
Upvote 0
Dear @lrobbo314

Your formula did work magically. I had difficulties to understand the logic of formula, but I will dig it out later on.
I created listbox in sheet which helps me putting '1's for selected parameters.

Thank you so much again, have a good day!
 
Upvote 0
I was able to pretty significantly reduce the size of one of the formulas. This is the formula for the data, not the headers.

Code: 
=IFERROR(INDEX($A$3:$A$12,SMALL(IF(OFFSET($A$1,2,MATCH(M$2,$B$2:$K$2,0),10,1)="X",ROW(INDIRECT("1:10"))),ROW($A1))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
666
ItalianPlatinum
I
R
  • Question Question
VBA Generate email with multiple data in body.
Replies
2
Views
132
Logit
Logit
R
  • Solved
TEXTSPLIT into Rows but Maintain Grouping
Replies
5
Views
112
Cubist
Cubist
C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
177
CLKinfo
C
W
  • Solved
Passing Multiple parameters to VBA macro from a form button
Replies
7
Views
317
dmt32
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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