Return Single Instance of Numeric Values from a Column

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
>

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)),"")

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
>
>

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

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

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
>

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)),"")

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
>
>

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

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

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

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
>

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)),"")

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
>
>

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

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