Return every 50th Value?
 

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

something like this maybe pasted down for the entire range? it should show
the number of the occurance for each cell

=COUNTIF(N3:$N$3,N3)

"Ken" wrote:

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

Paste the below formula in N3 since your data stars from cell N3.

=IF(COUNTIF($N$3:$N3,$N3)=1,"1ST OCCURRENCE","DUPLICATES")

Copy the Cell N3 and paste it to the remaining cells of N Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Ken" wrote:

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

To clarify ... I had previously tried this formula as an array:

{=if(countif($n$3:n3,n3)=1,n3,"")}

But then I had "blank" cells to deal with ... I do not want this.

I wish:

D3 ... return value from N3
D4 ... return value from N53
D5 ... return value from N103
D6 ... return value from N153
Etc ...

My "Thanks" for supporting these boards ... Kha

"Ms-Exl-Learner" wrote:

Paste the below formula in N3 since your data stars from cell N3.

=IF(COUNTIF($N$3:$N3,$N3)=1,"1ST OCCURRENCE","DUPLICATES")

Copy the Cell N3 and paste it to the remaining cells of N Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Ken" wrote:

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

In cell D3 type: =OFFSET($N$1,2+(ROW()-3)*50,)
and copy down
Micky


"Ken" wrote:

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

See http://www.cpearson.com/Excel/EveryNth.aspx for a variety of
formulas related to this very topic.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Fri, 29 Jan 2010 09:12:01 -0800, Ken
wrote:

Excel2003 ...

Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.

In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N

== I can get results using "Advanced Filter Unique Records", but I need to
do this by formula (if I can?)

Thanks ... Kha

Return every 50th Value?
 

Try this one other way which strips it dynamically:
In say D3, copied down: =INDEX($N$3:$N$5002,ROWS($1:1)*50-50+1)
The formula above is row independent, ie it can start in any cell in col D
Success? celebrate it, hit YES below
--
Max
Singapore
---
"Ken" wrote:
Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value
repeats 50 times.
In Col D ... I wish a formula to return 1st occs only of each Value found in
Col N