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?

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?

C1: =IF(COUNTIF($A$1:$A$5,A1)1,A1&" "&B1,"")

Drag the Fill handle from C1 to C5


" 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?

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.

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.

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?

yes...later today
--
Gary''s Student
gsnu200705


" wrote:

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?

Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub


Once again, erase any previous versions.
--
Gary''s Student
gsnu200705


" wrote:

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?

On Feb 12, 7:14 am, Gary''s Student
wrote:
Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

Once again, erase any previous versions.
--
Gary''s Student
gsnu200705



" wrote:
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
usingRank, 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?- Hide quoted text -

- Show quoted text -

Gary's Student . . it worked perfectly. I really can't thank you
enough . . but . . .THANK YOU! :)

On Feb 12, 10:52 am, wrote:
On Feb 12, 7:14 am, Gary''s Student





wrote:
Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

Once again, erase any previous versions.
--
Gary''s Student
gsnu200705

" wrote:
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
usingRank, 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?- Hide quoted text -

- Show quoted text -

Gary's Student . . it worked perfectly. I really can't thank you
enough . . but . . .THANK YOU! :)- Hide quoted text -

- Show quoted text -

Gary's Student, one last thing. The names that the macro is
concatenating are in the format "First Name" last name (ie John Doe)
so when it combines two names (or more) in a cell it looks like John
Doe Jane Doe. Is there anyway to get the macro to put a comma between
the names it combines (ie John Doe, Jane Doe, Bob Smith etc)?

Yes...later today
--
Gary''s Student
gsnu200705


" wrote:

On Feb 12, 10:52 am, wrote:
On Feb 12, 7:14 am, Gary''s Student





wrote:
Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

Once again, erase any previous versions.
--
Gary''s Student
gsnu200705

" wrote:
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
usingRank, 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?- Hide quoted text -

- Show quoted text -

Gary's Student . . it worked perfectly. I really can't thank you
enough . . but . . .THANK YOU! :)- Hide quoted text -

- Show quoted text -

Gary's Student, one last thing. The names that the macro is
concatenating are in the format "First Name" last name (ie John Doe)
so when it combines two names (or more) in a cell it looks like John
Doe Jane Doe. Is there anyway to get the macro to put a comma between
the names it combines (ie John Doe, Jane Doe, Bob Smith etc)?

Here is verson 4:

Sub a_grand_staff()
'
' VERSION 4
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If


End Sub


--
Gary''s Student
gsnu200706


" wrote:

On Feb 12, 10:52 am, wrote:
On Feb 12, 7:14 am, Gary''s Student





wrote:
Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

Once again, erase any previous versions.
--
Gary''s Student
gsnu200705

" wrote:
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
usingRank, 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?- Hide quoted text -

- Show quoted text -

Gary's Student . . it worked perfectly. I really can't thank you
enough . . but . . .THANK YOU! :)- Hide quoted text -

- Show quoted text -

Gary's Student, one last thing. The names that the macro is
concatenating are in the format "First Name" last name (ie John Doe)
so when it combines two names (or more) in a cell it looks like John
Doe Jane Doe. Is there anyway to get the macro to put a comma between
the names it combines (ie John Doe, Jane Doe, Bob Smith etc)?

On Feb 14, 11:25 am, Gary''s Student
wrote:
Here is verson 4:

Sub a_grand_staff()
'
' VERSION 4
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

--
Gary''s Student
gsnu200706



" wrote:
On Feb 12, 10:52 am, wrote:
On Feb 12, 7:14 am, Gary''s Student

wrote:
Sub a_grand_staff()
'
' VERSION 3
'
iC = 1
cString = 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
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
iC = iC + 1
If iC = 6 Then
Exit Sub
End If
cString = Cells(iA, "B").Value
End If
Next

If cString < "" Then
Cells(iC, "C").Value = cString
Cells(iC, "D").Value = Cells(iA - 1, "A").Value
End If

End Sub

Once again, erase any previous versions.
--
Gary''s Student
gsnu200705

" wrote:
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
usingRank, 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?- Hide quoted text -

- Show quoted text -

Gary's Student . . it worked perfectly. I really can't thank you
enough . . but . . .THANK YOU! :)- Hide quoted text -

- Show quoted text -

Gary's Student, one last thing. The names that the macro is
concatenating are in the format "First Name" last name (ie John Doe)
so when it combines two names (or more) in a cell it looks like John
Doe Jane Doe. Is there anyway to get the macro to put a comma between
the names it combines (ie John Doe, Jane Doe, Bob Smith etc)?- Hide quoted text -

- Show quoted text -

Thank you yet again!