Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return date if in range, else return blank | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
Pivot Table - 50th Percentile instead of Average | Excel Discussion (Misc queries) | |||
check if reference exists, then return its value or return 0 | Excel Worksheet Functions |