VBA code to find matching values

[snjpverma]

Names	Guess
John	5
Tom	5
Addy	6
Abby	5
Daddy	8
Final Result	5

There are three people's values that are matching the final Result. Msgbox required to show the names of the person whose value matches the final result.

Output: Msgbox is required as shown below:

The above Msgbox has been created manually for demo purpose.

Any help is highly appreciated.

 

[Fluff]

Bit late, just for the fun of it, here's my solution.

Sub snjpverma()
   Dim x As Variant
   x = Join(Filter([transpose(if(b2:b6=b9,a2:a6,false))], False, False), ",")
   MsgBox StrReverse(Replace(StrReverse(x), ",", " & ", 1, 1))
End Sub

 

[snjpverma]

Bit late, just for the fun of it, here's my solution.

Sub snjpverma()
   Dim x As Variant
   x = Join(Filter([transpose(if(b2:b6=b9,a2:a6,false))], False, False), ",")
   MsgBox StrReverse(Replace(StrReverse(x), ",", " & ", 1, 1))
End Sub

Wow, this seems pure magic. So elegant!

I'll study this later when time permits. Thanks so much Fluff.

 

[Fluff]

You're welcome & thanks for the feedback

 

[snjpverma]

Bit late, just for the fun of it, here's my solution.

Sub snjpverma()
   Dim x As Variant
   x = Join(Filter([transpose(if(b2:b6=b9,a2:a6,false))], False, False), ",")
   MsgBox StrReverse(Replace(StrReverse(x), ",", " & ", 1, 1))
End Sub

Hi fluff,
I just tried to incorporate this in a different workbook by giving it the worksheet name "Calc" and it says type mismatch on the line highlighted in Red.
Also, instead of msgbox I gave the final output to the variable x again, as highlighted in Green below. I hope that won't cause any problem to the code further. Right now, i am unable to check that due to type mismatch on the Red line.
I have planned to put this text in a cell rather than in a msgbox.

Dim x as variant
x = Join(Filter([transpose(if(calc.range("b2:b6")=calc.range("b9"),calc.Range("a2:a6"),false))], False, False), ",")
x = StrReverse(Replace(StrReverse(x), ",", " & ", 1, 1))
calc.range("c15") = x

 

[Fluff]

You need to do it like

   x = Join(Filter([transpose(if(raw!b2:b6=raw!b9,raw!a2:a6,false))], False, False), ",")

where "raw" is the actual sheet name

 

[Fluff]

If you want to do it like you tried, it would need to be

   x = Join(Filter(calc.Evaluate("transpose(if(" & Range("B2:B6").Address & "=" & Range("B9").Address & "," & Range("A2:A6").Address & ",false))"), False, False), ",")

 

[snjpverma]

If you want to do it like you tried, it would need to be

   x = Join(Filter(calc.Evaluate("transpose(if(" & Range("B2:B6").Address & "=" & Range("B9").Address & "," & Range("A2:A6").Address & ",false))"), False, False), ",")

Thanks Fluff. It's sorted.
It wouldn't be possible without your support.
I realized that when the values in B2:B6 were errors it was giving a type mismatch error.
The moment I used Iferror and replaced the errors with "" , your code worked.

Thanks again.

 

[Fluff]

Glad you sorted it & thanks for the feedback