Find Duplicates and place in a single row

[Ziggy21]

Hi

I have a spreadsheet (Excel 2003) with a list of names in column A and the groups they belong to in column B.

As some people are in several groups I have a lot of duplicates in column A.

I want to detect the duplicate names in column A and delete them, but move the cells which name the groups they belong to from column B into new cells in a single row which will be the first row where a duplicated name is mentioned.

I probably could have explained that a lot better, sorry - example sheet attached

There are 14000 names in the list

Thanks in Advance

[icestationzbra]

see if this helps:

http://www.excelforum.com/excel-gene...to-column.html

[Paul]

Hi Ziggy,

You can use either formulas or macros for this task. Here's the formula method, which uses a helper column.

Assuming your original list is in columns A:B:

1. Click Data > Filter > Advanced Filter.
2. Beneath 'Action', select 'Copy to another location.
3. Next to 'List Range', type the range of your data in column A, e.g. $A$1:$A$14000. (You could also click the button to the right of that textbox and select your data range instead.)
4. Next to 'Copy to', type (or select) cell $D$1.
5. Check the box for 'Unique records only' and click OK.

The result will be a unique list of your names in column D. Now to get the groups they belong to:

1. Insert a new column to the left of column A (your original data will shift to B:C, and your unique list will be in E).
2. In the new A2, enter this formula:

=B2&COUNTIF($B$2:$B2,B2)

3. Fill that down to A14000 (or your last row of data).
4. In cell F2 (to the right of your first unique name), enter this formula:

=IF(ISERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$14000,3,FALSE)),"",VLOOKUP($E2&COLUMN(A1),$A$1:$C$14000,3,FALSE))

5. Fill that down as many rows as you have unique names, and then fill to the right for as many groups as any individual may belong to. If no individual will belong to more than, say, 5 groups, you only need to fill the formulas to the right 5 columns.

Hopefully that helps.

If calculation becomes an issue, you may want to try the macro route (which we can also assist with). Here's a link to a page that shows both options (would need a little customization): http://www.ozgrid.com/Excel/find-nth.htm

[Ziggy21]

Awesome

Thanks a lot that works, although after a small test, I have now set it doing the whole lot and its grinding away slowly, so I may well be back for help with the other method.

Thanks Again