Return every 50th Value?

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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)
 
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!
 
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
 
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
 
Back
Top