Creating a list from Table Data

G

Guest

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.
 
G

Guest

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
 
G

Guest

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
 
G

Guest

Can you use this code with a command button?

Art said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top