If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Return Single Instance of Numeric Values from a Column
Hi All,
Column "P" contains a list of values that may appear more than once. I would like to have only ONE instance of each value returned from Column "P" (Rows 2: 40) to Column "E" (Rows 50:88) in ascending order without blank Rows from where the duplicates would have been. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200508/1 |
#2
|
|||
|
|||
From the Data menu select Filter Advanced Filter. Select Copy to Another
Location. Select P1:P40 as the list Range. Enter E50 as Copy To Range. Check Unique Records Only. Click OK. Then select Range E50:E88 or whatever is last row and select Data Sort. Hope this helds Rowan "Sam via OfficeKB.com" wrote: Hi All, Column "P" contains a list of values that may appear more than once. I would like to have only ONE instance of each value returned from Column "P" (Rows 2: 40) to Column "E" (Rows 50:88) in ascending order without blank Rows from where the duplicates would have been. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200508/1 |
#3
|
|||
|
|||
Q2:
=RANK(P2,$P$2:$P$40) Q3, copied down: =IF(ISNUMBER(MATCH(P3,$P$2:P2,0)),"",RANK(P3,$P$2: $P$40)) R1: =MAX($R$2:$R$40) R2, copied down: =IF(N(Q2),RANK(Q2,$Q$2:$Q$40),"") E50, copied down: =IF(ROWS($E$50:E50)=$R$1,INDEX($P$2:$P$40,MATCH(R OWS($E$50:E50),$R$2:$R$40,0)),"") Sam via OfficeKB.com wrote: Hi All, Column "P" contains a list of values that may appear more than once. I would like to have only ONE instance of each value returned from Column "P" (Rows 2: 40) to Column "E" (Rows 50:88) in ascending order without blank Rows from where the duplicates would have been. Thanks Sam |
#4
|
|||
|
|||
Hi Aladin,
Thank you very much for your assistance: your Formulae provided the desired results. Cheers, Sam Aladin Akyurek wrote: Q2: =RANK(P2,$P$2:$P$40) Q3, copied down: =IF(ISNUMBER(MATCH(P3,$P$2:P2,0)),"",RANK(P3,$P$2 :$P$40)) R1: =MAX($R$2:$R$40) R2, copied down: =IF(N(Q2),RANK(Q2,$Q$2:$Q$40),"") E50, copied down: =IF(ROWS($E$50:E50)=$R$1,INDEX($P$2:$P$40,MATCH( ROWS($E$50:E50),$R$2:$R$40,0)),"") Aladin Akyurek wrote: Q2: =RANK(P2,$P$2:$P$40) Q3, copied down: =IF(ISNUMBER(MATCH(P3,$P$2:P2,0)),"",RANK(P3,$P$2 :$P$40)) R1: =MAX($R$2:$R$40) R2, copied down: =IF(N(Q2),RANK(Q2,$Q$2:$Q$40),"") E50, copied down: =IF(ROWS($E$50:E50)=$R$1,INDEX($P$2:$P$40,MATCH( ROWS($E$50:E50),$R$2:$R$40,0)),"") Hi All, [quoted text clipped - 5 lines] Thanks Sam -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
Hi Rowan,
Thank you for your solution. As the values frequently change I've gone with Aladin's Formulae solution. Cheers, Sam Rowan wrote: From the Data menu select Filter Advanced Filter. Select Copy to Another Location. Select P1:P40 as the list Range. Enter E50 as Copy To Range. Check Unique Records Only. Click OK. Then select Range E50:E88 or whatever is last row and select Data Sort. Hope this helds Rowan Hi All, [quoted text clipped - 5 lines] Thanks Sam -- Message posted via http://www.officekb.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to return # characters based on 2nd instance of value | JDay01 | Worksheet Functions | 4 | May 19th, 2005 05:29 PM |
How to use SUMIF to return sums between two values located in cells | ScottBerger | Worksheet Functions | 1 | November 18th, 2004 07:09 PM |
Converting Text to numeric values | MichaelStarman | Worksheet Functions | 5 | January 29th, 2004 07:56 PM |
How to Convert Numeric Values into Text Values with a Formula | Vijay | Worksheet Functions | 3 | November 10th, 2003 03:12 PM |
return value in col C based on values in Cols A & B | itchyw | Worksheet Functions | 1 | September 18th, 2003 06:22 PM |