Create Summary highlights

[jp77]

I have the table as shown below:

John 75%
David 95%
Mike 88%

It can display 3 names or 5 name or 2 name, but no more than 10. In other words, it displays a result dynamically.

Using VB, I would like to create a text highlight summary like

"Top Students are Daivd (95%), Mike (88%), and John (75%)"

Thank you for all your help!!!!

 

[ageren]

i assume that your data range is as follow;

A1 = Turkiye 93%
A2 = David 95%
A3 = Mike 88%
A4 = Ali 90%
A5 = john 75%
A6 = Hasan 77%

watch the result into cell B1

try this;

Sub ogrenci_bul()
son = [a65536].End(3).Row
For i = 1 To son
Range("B" & i).Value = Right(Range("A" & i), 3)
Next i
Range("A1:B" & son).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("b:B").ClearContents
Range("b1").Value = " Top Students are " & [a1] & " ," & [a2] & " and " & [a3]

End Sub

 

[jp77]

Hi ageren,

Thank you for your help. I think my explanation was not clear. The result can be top 3 or top 4 or 5 or 2.. In my example, if the range displays 4 names, it would be Top 4...so on... In your example would be Top 6 and display the names with the last one proceeding with "and" before.

Thanks!!!!!! JP77

 

[ageren]

pls try like that;

A1 = Turkiye 93%
A2 = David 95%
A3 = Mike 88%
A4 = Ali 90%
A5 = john 75%
A6 = Hasan 77%
A7 = Onur 56%
A8 = Volkan 80%

( your data range will be the same but, the result will be seen at cell A1 )

Sub ogrenci_bul()
son = [a65536].End(3).Row
sayi = WorksheetFunction.CountA(Range("A:A"))
For i = 1 To son
Range("B" & i).Value = Right(Range("A" & i), 3)
Next i
Range("A1:B" & son).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B:B").ClearContents
Range(Range("A1"), Range("A" & sayi)).Copy
Range("B1").PasteSpecial Transpose:=True
Application.CutCopyMode = False
Range("A:A").ClearContents
For i = 1 To sayi
If [a1] = ", " Then [a1].ClearContents
[a1] = [a1] & ", " & Cells(1, i).Value
Next i
[a1] = "Top students are " & [a1] & " and " & Cells(1, i).Value
Range("b1:s3").ClearContents
Range("a1").Select
End Sub

 

[jp77]

Worked Marvelously!!!! THANK YOU!!!!!!!!!!

 

[ageren]

your welcome, glad to if it was usefull for you...