# Create Summary highlights

#### jp77

##### Board Regular
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!!!!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### ageren

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
Worked Marvelously!!!! THANK YOU!!!!!!!!!!

Replies
0
Views
349
Replies
7
Views
434
Replies
1
Views
532
Replies
1
Views
486
Replies
1
Views
298

1,190,786
Messages
5,982,913
Members
439,807
Latest member
WXM86

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

### Which adblocker are you using?

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

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