Help sorting duplicate data

[emilyshaw93]

Hi there,

I have an excel problem. I have set up quite a complex workbook over multiple sheets.

I have a list of names from one sheet, as below:

Adam 1
Emily 3
Emily 3
Emily 3
John 2
John 2

Basically, this counts the number of times a name is mentioned in a previous sheet. What I want to happen is that if a name is mentioned AT LEAST once, to move it into another list, but only to move one name (ie only 1 Emily and 1 John), not all references to it.

I have set the system up so that the formula works for just 1 mention, using the formula below:
=IF('NAMES'!I4=1,'NAMES'!B4)

Where I4 refers to the counting function set up, and B4 refers to the corresponding name itself.

So basically, if used AT LEAST once, copy the name across, but ONLY ONCE.

Any help much appreciated!

 

[AlanY]

you can just copy col B (names) to the new sheet and use excel's built-in function > Data > Remove duplicates to get your unique list of names

 

[emilyshaw93]

Hi Alan,

Thanks for this. What I wanted though was not to remove the duplicates, but to automatically copy JUST the first time 'Emily' for example, is referred to, if it is referred to more than once.

Any ideas?

E

 

[AlanY]

as you're copy them across to another sheet, the duplicates wouldn't get deleted.
anyway, formula in D2 and copy down will get the unique list

Excel 2012
	A	B	C	D
1				name
2	Adam	1		Adam
3	Emily	3		Emily
4	Emily	3		John
5	Emily	3
6	John	2
7	John	2
8

Cell Formulas
Range	Formula
D2	{=INDEX($A$2:$A$100, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$100), 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.