I tried some things (functions: MATCH & COUNTIF) and have the
following comments and suggested solution.
First, VLOOKUP finds the first occurrance, butalso requires that
the table be sorted so column 1 is in ascending order. This will also
put all same names together which helps obtain your desired result.
Then, the Function COUNTIF can find how many times a Name is in
your table -- and you can get their numbered locatons.
You could do the following.
0- Add another column which is numberd sequentially (just as your
column B happens to be) I'll call it Column "C"
1- Sort the *WHOLE* table (all three columns) by Name, ascending. If
you need it in the original order you can simply "un-sort" by the
column "C" (added in the above step). Or copy the table to another
location or sheet and sort only one of the copies.
2- Use VLOOKUP to locate the first occurrance of a name, in the
*SORTED* list. You'll have to decide just how you want to note this
location. You can add yet another column which is numbered
sequentially AFTER the sort. OR you can use the ROW number of the
first occurrance.
3- Use the function COUNTIF to determine how many of a given Name is
in the Name column of the table.
For example, paste this into your cell "G3":
=COUNTIF(A2:A14,C3)
and copy it down for 4 more cells to obtain a count of each of your
names. It will show values of 1, 2, 3, 3, 2 in your sheet.
4- Use a loop to read the location of the occurrances by looking in
the addeed column "C". Do this for the next "COUNTIF" occurrances;
that is, the number of them given by the COUNTIF.
It will look *Something* like this Pseudo Code:
= = = = = = = = Find items = = = = = = = = = =
Dim Location As Integer
Dim X As Integer
Add sequentially numbered column "C".
Sort *complete* Table By Col "C"
Location = First location (Row) of "Name" found with VLOOKUP
Data_Required = "" 'Start with an empty String.
For X = 0 to [THE COUNTIF value]
Select cell offset from "Location" by X rows. (Offset Method)
Data_Required = Data_Required & ", " & Column "C".Value
' The above builds your numbered list W/ commas.
Next
Save Data_Required String in your column "E" (labeld data Required).
Note:
Although the variable "Location" is a number (Integer) and the
Data_Required" is a String, VBA will do the conversion from number to
String for you in the last line. This allows using the String
Concatination operator "&". If you want to do it explicitly, use the
Convert to String function "Cstr()". The last line then looks like this.
Data_Required = Data_Required & ", " & Cstr(Column "C".Value)