Return every 50th Value?

[Ken]

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

 

[joemeshuggah]

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)

 

[Ms-Exl-Learner]

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!

 

[Ken]

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Chip Pearson]

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]

 

[Max]

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