Creating a list from Table Data
 

I have a table of Row = "Invested $" (ie. 10%, 20%...,50%) vs. Column =
"Priority" (ie. 1,2,...,5).
The values are scores and I have a "given value". I want to create a list
or table that shows the Row title (10%) and Column title (1) for all the
values in the table that are greater than the "given value".

I've been trying different ways with INDEX, VLOOKUP and other functions with
no luck yet.

I tried unsuccesfully to do this with a formula. You could accomplish this
with a VBA macro as follows:

Option Explicit

Sub temp()
Dim mGiven As Double
Dim mCol As Integer
Dim mRow As Integer
Dim mOutputRow As Integer

mGiven = Cells(8, 1)
mOutputRow = 9
For mRow = 2 To 6
For mCol = 2 To 6
If Cells(mRow, mCol) mGiven Then
Cells(mOutputRow, 1) = Cells(1, mCol)
Cells(mOutputRow, 2) = Cells(mRow, 1)
mOutputRow = mOutputRow + 1
End If
Next mCol
Next mRow
End Sub

This assumes that your scores are in B2:F6, your percents are in B1:F1, your
priorities are in A2:A6 and your given is in A8. It will put your table in
A9 through whatever.

Good luck.

Art

"bry32" wrote:

I have a table of Row = "Invested $" (ie. 10%, 20%...,50%) vs. Column =
"Priority" (ie. 1,2,...,5).
The values are scores and I have a "given value". I want to create a list
or table that shows the Row title (10%) and Column title (1) for all the
values in the table that are greater than the "given value".

I've been trying different ways with INDEX, VLOOKUP and other functions with
no luck yet.

Can this code be used to work on the click of a command button?

"Art" wrote:

I tried unsuccesfully to do this with a formula. You could accomplish this
with a VBA macro as follows:

Option Explicit

Sub temp()
Dim mGiven As Double
Dim mCol As Integer
Dim mRow As Integer
Dim mOutputRow As Integer

mGiven = Cells(8, 1)
mOutputRow = 9
For mRow = 2 To 6
For mCol = 2 To 6
If Cells(mRow, mCol) mGiven Then
Cells(mOutputRow, 1) = Cells(1, mCol)
Cells(mOutputRow, 2) = Cells(mRow, 1)
mOutputRow = mOutputRow + 1
End If
Next mCol
Next mRow
End Sub

This assumes that your scores are in B2:F6, your percents are in B1:F1, your
priorities are in A2:A6 and your given is in A8. It will put your table in
A9 through whatever.

Good luck.

Art

"bry32" wrote:

I have a table of Row = "Invested $" (ie. 10%, 20%...,50%) vs. Column =
"Priority" (ie. 1,2,...,5).
The values are scores and I have a "given value". I want to create a list
or table that shows the Row title (10%) and Column title (1) for all the
values in the table that are greater than the "given value".

I've been trying different ways with INDEX, VLOOKUP and other functions with
no luck yet.

Sure,

Just create a command button on your sheet and paste the code into it.
Don't paste the "sub temp()" or the "end sub" -- when you create your command
button something like "Private Sub CommandButton1_Click()" will appear and
this will substitute for "sub temp()"

Do your pasting between that and the "end sub"

Art

"bry32" wrote:

Can this code be used to work on the click of a command button?

"Art" wrote:

I tried unsuccesfully to do this with a formula. You could accomplish this
with a VBA macro as follows:

Option Explicit

Sub temp()
Dim mGiven As Double
Dim mCol As Integer
Dim mRow As Integer
Dim mOutputRow As Integer

mGiven = Cells(8, 1)
mOutputRow = 9
For mRow = 2 To 6
For mCol = 2 To 6
If Cells(mRow, mCol) mGiven Then
Cells(mOutputRow, 1) = Cells(1, mCol)
Cells(mOutputRow, 2) = Cells(mRow, 1)
mOutputRow = mOutputRow + 1
End If
Next mCol
Next mRow
End Sub

This assumes that your scores are in B2:F6, your percents are in B1:F1, your
priorities are in A2:A6 and your given is in A8. It will put your table in
A9 through whatever.

Good luck.

Art

"bry32" wrote:

I have a table of Row = "Invested $" (ie. 10%, 20%...,50%) vs. Column =
"Priority" (ie. 1,2,...,5).
The values are scores and I have a "given value". I want to create a list
or table that shows the Row title (10%) and Column title (1) for all the
values in the table that are greater than the "given value".

I've been trying different ways with INDEX, VLOOKUP and other functions with
no luck yet.

Can you use this code with a command button?

"Art" wrote:

I tried unsuccesfully to do this with a formula. You could accomplish this
with a VBA macro as follows:

Option Explicit

Sub temp()
Dim mGiven As Double
Dim mCol As Integer
Dim mRow As Integer
Dim mOutputRow As Integer

mGiven = Cells(8, 1)
mOutputRow = 9
For mRow = 2 To 6
For mCol = 2 To 6
If Cells(mRow, mCol) mGiven Then
Cells(mOutputRow, 1) = Cells(1, mCol)
Cells(mOutputRow, 2) = Cells(mRow, 1)
mOutputRow = mOutputRow + 1
End If
Next mCol
Next mRow
End Sub

This assumes that your scores are in B2:F6, your percents are in B1:F1, your
priorities are in A2:A6 and your given is in A8. It will put your table in
A9 through whatever.

Good luck.

Art

"bry32" wrote:

I have a table of Row = "Invested $" (ie. 10%, 20%...,50%) vs. Column =
"Priority" (ie. 1,2,...,5).
The values are scores and I have a "given value". I want to create a list
or table that shows the Row title (10%) and Column title (1) for all the
values in the table that are greater than the "given value".

I've been trying different ways with INDEX, VLOOKUP and other functions with
no luck yet.