On Feb 11, 3:36 pm, Gary''s Student
wrote:
Glad your response included an example. It helped me understand the
situation a little better. Here is the updated code:

Sub a_grand_staff()
'
' VERSION 2
'
iC = 1
cString = Cells(1, "A").Value & " " & Cells(1, "B").Value
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For iA = 2 To LastRow
If Cells(iA, "A").Value = Cells(iA - 1, "A").Value Then
cString = cString & " " & Cells(iA, "B").Value
Else
Cells(iC, "C").Value = cString
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "A").Value & " " & Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
End If

End Sub

REMEMBER: delete the old version prior to installing the new version.
--
Gary's Student
gsnu200705



" wrote:
On Feb 11, 6:00 am, Gary''s Student
wrote:
Sub a_grand_staff()
iC = 1
cString = Cells(1, "B").Value
For iA = 2 To 5
If Cells(iA, "A").Value = Cells(iA - 1, "A").Value Then
cString = cString & " " & Cells(iA, "B").Value
Else
Cells(iC, "C").Value = cString
iC = iC + 1
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
End If

End Sub

This little macro takes the first five entries in column A and moves the
names in column B to column C. If, however there are duplicate entries in
column A, the names in B are concatenated prior to being placed in column C.

Macros are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary's Student
gsnu200705

" wrote:
I have a simple table, two columns A and B. In B1:B40 I have a list of
names, in A1:A40 are a list of numbers sorted from highest to lowest.
Ultimately what I'm trying to do is to get a ranking of the top 5
scores but, when there is a tie (duplicates in A) to take the names in
B next to the duplicate scores and Concatenate them in C. I've tried
using Rank, Vlookup, using Excel's FILTER and none of them work for
what I'm trying to do. My thought is to try to find a function that
will look through the numbers in A and when it finds duplicates
concatenate the matching names in a cell in C but as Vlookup only
returns one result it isn't working. Anyone have any ideas?- Hide quoted text -

- Show quoted text -

Gary's Student,

Thanks very much for that code. It worked . . mostly. There's a piece
I think I didn't explain well enough in my first post. One of the
problems with these rankings is that the 'Top 5' scores could actually
be down through C6 or hypothetically, even C10 or lower (see example
below):

First line ("23 John Doe" is Row 1)
(example 1)

A B
23 John Doe
22 Jane Doe
22 Bob Jones
21 John Smith
20 Lisa Johnson
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Williams
18 Luke Jenkins
17 April Jones

The results I am looking for would be:
(example 2)

A B
C D
23 John Doe John
Doe 23
22 Jane Doe Jane Doe, Bob
Jones 22
22 Bob Jones John
Smith 21
21 John Smith Lisa Johnson, Sue
Jones 20
20 Lisa Johnson Cathy Stanford, Jack Ford, Bill
Williams 19
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Williams
18 Luke Jenkins
17 April Jones

So in example 2, it is finding the top scores, even down through the
duplicates, and concatenating the names in C (I forgot to mention in
the first post I also need to put the number for column A next to the
result in C. The Macro you posted definitely did the Concatenating
piece perfectly, but it only went down through the top 5 lines, not
the top 5 scores. Since the top 5 scores could go all the way down
fairly far down column A (if there were a large number of ties) is
there any way to make it look down through the list to find the top 5?

Below is what the Macro you posted returned:

A B
C
23 John Doe John
Doe
22 Jane Doe Jane Doe, Bob
Jones
22 Bob Jones John
Smith
21 John Smith Lisa Johnson, Sue
Jones
20 Lisa Johnson
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Williams
18 Luke Jenkins
17 April Jones

which only returned the top 4 scores.- Hide quoted text -

- Show quoted text -

In this one it is concatenating the score (from column A) in the same
cell (column C) as the names (from column B). Any way to make it so
the score from Column A goes into Column D in the cell to the right of
the concatentaed names?