Compare column of number

Novice Lee · External Since: Oct 21, 2009 Posts: 2

Hello,
Is there a way to compare numbers in a column and return the numbers once.
Example:
a b c d e f
300 300 310 312 400 411
310
300
312
411
400
400
400

Thanks

Max · External Since: Mar 17, 2004 Posts: 1751

Source data assumed running in A1 down
In B1: =IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))
Copy B1 down to cover the max expected extent of source data, say down to
B200?

Then in C1:
=IF(COLUMNS($A:A)>COUNT($B:$B),"",INDEX($A:$A,SMALL($B:$B,COLUMNS($A:A))))
Copy C1 across to cover the max expected number of unique items, eg across
to Z1?. C1 across returns the results you seek. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Novice Lee" wrote:
> Is there a way to compare numbers in a column and return the numbers once.
> Example:
> a b c d e f
> 300 300 310 312 400 411
> 310
> 300
> 312
> 411
> 400
> 400
> 400
>
> Thanks

Novice Lee · External Since: Oct 21, 2009 Posts: 2

insted of doing the whole column how can I get it do ranges of numbers like

A4:A28, A31:A55, A58:A82, etc.....

"Max" wrote:

> Source data assumed running in A1 down
> In B1: =IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))
> Copy B1 down to cover the max expected extent of source data, say down to
> B200?
>
> Then in C1:
> =IF(COLUMNS($A:A)>COUNT($B:$B),"",INDEX($A:$A,SMALL($B:$B,COLUMNS($A:A))))
> Copy C1 across to cover the max expected number of unique items, eg across
> to Z1?. C1 across returns the results you seek. Success? hit the YES below
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> ---
> "Novice Lee" wrote:
> > Is there a way to compare numbers in a column and return the numbers once.
> > Example:
> > a b c d e f
> > 300 300 310 312 400 411
> > 310
> > 300
> > 312
> > 411
> > 400
> > 400
> > 400
> >
> > Thanks

Max · External Since: Mar 17, 2004 Posts: 1751

Here's how, an example for > A4:A28 ..

In B4: =IF(A4="","",IF(COUNTIF(A$4:A4,A4)>1,"",ROW()))
Copy down to B28

In C4:
=IF(COLUMNS($A:A)>COUNT($B$4:$B$2 Cool

,"",INDEX($A:$A,SMALL($B$4:$B$28,COLUMNS($A:A))))
Copy across as far as required to cover the max expected number of unique
items from the source range, say to C20?
Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Novice Lee" wrote:
> insted of doing the whole column how can I get it do ranges of numbers like
>
> A4:A28, A31:A55, A58:A82, etc.....