VBA Match & index with conditions

E

eaje92

New Member
Joined
Feb 19, 2018
Messages
16
Hi all, i am currently trying to create a counter which can help me select an action (A,B,C) depending on the highest value in that state.

Example:
First, the random counter would random between "HIGHEST" or "RANDOM". The probability of the random counter would be 90% "highest" and 10% "random". How i went about doing this is

randomcounter = Int((100 - 1 + 1) * Rnd + 1)
If epsilon > 10 Then
counter = "Q"
Else
counter = "random"
End If

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Random Counter[/TD]
[TD]HIGHEST OR RANDOM[/TD]
[/TR]
</tbody>[/TABLE]

After the random counter has chosen between the 2. If it chose "HIGHEST", then based on a state(i would have determined this before), it would chose the highest value and give the corresponding action.

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Action A[/TD]
[TD]Action B[/TD]
[TD]Action C[/TD]
[/TR]
[TR]
[TD]State1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]State2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]State3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]State4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]State5[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]State6[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]State7[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]State8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]State9[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]State10[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]














In this example, my current state is State5. If my counter randoms to a "HIGHEST", im trying to get vba to store "Action C" (being the highest out of 4,6,7).

If the counter randoms to a "Random", the action stored would random between the 2 other action,not the highest value which is "Action A" or "Action C".

i am currently stuck using an index match formula in excel but im trying to implement in vba coding. One way i could think of would be:
If randomcounter = "Highest" and State = 1 Then
Actiontaken = max(range("B6:D6"))

thats really all i got..
Your help is much appreciated!
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
See if this does what you want


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
Action A​
[/td][td]
Action B​
[/td][td]
Action C​
[/td][td][/td][td]
State​
[/td][td]
Random or Highest​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
State1​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td][/td][td]
State5​
[/td][td]
Random​
[/td][td]
Action B​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
State2​
[/td][td]
2​
[/td][td]
1​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
State3​
[/td][td]
1​
[/td][td]
2​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
State4​
[/td][td]
1​
[/td][td]
2​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
State5​
[/td][td]
4​
[/td][td]
6​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
State6​
[/td][td]
1​
[/td][td]
8​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
State7​
[/td][td]
2​
[/td][td]
5​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
State8​
[/td][td]
0​
[/td][td]
0​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
State9​
[/td][td]
2​
[/td][td]
0​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
State10​
[/td][td]
8​
[/td][td]
6​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Given F2 (State)

Formula in G2
=IF(RANDBETWEEN(1,100)>10,"Highest","Random")

Formula in H2
=INDEX(B1:D1,MATCH(LARGE(INDEX(B2:D11,MATCH(F2,A2:A11,0),0),IF(G2="Highest",1,RANDBETWEEN(2,3))),INDEX(B2:D11,MATCH(F2,A2:A11,0),0),0))

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
See if this does what you want


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Action A​
[/TD]
[TD]
Action B​
[/TD]
[TD]
Action C​
[/TD]
[TD][/TD]
[TD]
State​
[/TD]
[TD]
Random or Highest​
[/TD]
[TD]
Result​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
State1​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD]
State5​
[/TD]
[TD]
Random​
[/TD]
[TD]
Action B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
State2​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
State3​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
State4​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
State5​
[/TD]
[TD]
4​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
State6​
[/TD]
[TD]
1​
[/TD]
[TD]
8​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
State7​
[/TD]
[TD]
2​
[/TD]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
State8​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
State9​
[/TD]
[TD]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
State10​
[/TD]
[TD]
8​
[/TD]
[TD]
6​
[/TD]
[TD]
0​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Given F2 (State)

Formula in G2
=IF(RANDBETWEEN(1,100)>10,"Highest","Random")

Formula in H2
=INDEX(B1:D1,MATCH(LARGE(INDEX(B2:D11,MATCH(F2,A2:A11,0),0),IF(G2="Highest",1,RANDBETWEEN(2,3))),INDEX(B2:D11,MATCH(F2,A2:A11,0),0),0))

Hope this helps

M.
Click to expand...

Hi marcelo, thanks but i would like to store the action taken in vba instead of using formula
 
Upvote 0
Maybe...

Code: 
Sub aTest()
    Dim rData As Range, RandomCounter As Long, k As Long
    Dim State As String, lRow As Range, myVal As Variant
    Dim Action As String
    
    'Given
    State = "State5"
    
    'Set data range
    Set rData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    'Get the row that contains State
    Set lRow = rData.Columns(1).Find(State, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If lRow Is Nothing Then
        MsgBox "State Not Found"
        Exit Sub
    End If
        
    'Generate a random integer between 1 - 100
    RandomCounter = Application.RandBetween(1, 100)
    If RandomCounter > 10 Then
        k = 1
    Else
        k = Application.RandBetween(2, 3)
    End If
    myVal = Application.Large(lRow.Resize(, 4), k)
    Action = rData.Rows(1).Cells(lRow.Resize(, 4).Find(myVal, lookat:=xlWhole, LookIn:=xlValues).Column)
End Sub

M.
 
Upvote 0
Marcelo Branco said:
Maybe...

Code: 
Sub aTest()
    Dim rData As Range, RandomCounter As Long, k As Long
    Dim State As String, lRow As Range, myVal As Variant
    Dim Action As String
    
    'Given
    State = "State5"
    
    'Set data range
    Set rData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    'Get the row that contains State
    Set lRow = rData.Columns(1).Find(State, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If lRow Is Nothing Then
        MsgBox "State Not Found"
        Exit Sub
    End If
        
    'Generate a random integer between 1 - 100
    RandomCounter = Application.RandBetween(1, 100)
    If RandomCounter > 10 Then
        k = 1
    Else
        k = Application.RandBetween(2, 3)
    End If
    myVal = Application.Large(lRow.Resize(, 4), k)
    Action = rData.Rows(1).Cells(lRow.Resize(, 4).Find(myVal, lookat:=xlWhole, LookIn:=xlValues).Column)
End Sub

M.
Click to expand...

hi Marcelo, thanks for your help but this isnt really what im looking for. After finding the row that the state is in, is there a to search for the Action with the highest value?
 
Upvote 0
eaje92 said:
hi Marcelo, thanks for your help but this isnt really what im looking for. After finding the row that the state is in, is there a to search for the Action with the highest value?
Click to expand...

The code does what you requested in post 1. Once the row is found, it gets the highest value and corresponding action (probability 90%) or the second or third largest (probability of 5% each) and the corresponding actions.

M.
 
Upvote 0
Marcelo Branco said:
The code does what you requested in post 1. Once the row is found, it gets the highest value and corresponding action (probability 90%) or the second or third largest (probability of 5% each) and the corresponding actions.

M.
Click to expand...

hmm im always stuck in "state not found"

Because my table is found from range A18:D30, i changed part of the code to Set rData = Range("A18:D" & Cells(Rows.Count, "A").End(xlUp).Row)
and State = Range("L10").Value as my state would dynamically change over time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
166
Ol Reliable
O
A
  • Question Question
Random Sampling Macro multiple criteria and loops - using arrays and dictionary
Replies
0
Views
735
Absurdk
A
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
559
rollis13
rollis13
C
  • Question Question
Formula to parse data correctly without pivot table
Replies
6
Views
321
Fluff
Fluff
I
  • Question Question
How to automatically update macro after adding a row?
Replies
0
Views
535
inxcndd
I

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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