finding differing numbers.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5

Thanks

Chris
 
Hi
one way:
- select your column
- choose 'Data - Filter Advanced Filter'
- choose a new range and 'unique entries'
 
Here's a very simple and effective way...

Suppose 100 such numbers in cells A1:A100

=SUM(1/COUNTIF(A1:A100, A1:A100))

This formula (it's an array formula, so whould be
CTRL+SHIFT+Enter'ed) will give you the number of different
items in the group.

But now I see that you want the actual list. Then I think
a macro like this will be necessary:

Sub lister()
cells(1,2)=cells(1,1)
n=2
For i=2 to 100
For j=1 to i-1
If cells(i,1)=cells(j,1) then Exit For
Next j
If j=i then cells(n,2)=cells(j,1):n=n+1
next i
End Sub

'It assumes the data is in cells A1:A100, will put the new
list from B1 downwards.

Regards
-----Original Message-----
How do I in a column of numbers some in duplication, how
Click to expand...
can i get a list off the entries which reflects these
numbers but not in duplication. ie "numbers" 1, 1, 2, 3,
4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5.
 
Back
Top