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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
143
CLKinfo
C
W
  • Solved
Passing Multiple parameters to VBA macro from a form button
Replies
7
Views
179
dmt32
D
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
305
John_w
J
D
  • Solved
Filter on decremental list
Replies
5
Views
94
Peter_SSs
Peter_SSs
R
  • Question Question
Auto filling a cell in a table which has a list using data validation
2
Replies
14
Views
163
rjn008
R

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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