EXTRACTING UNIQUE RECORD BASED ON CONDITION

This is a multi-part message in MIME format.

------=_NextPart_000_0012_01C781BF.08FB92F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello everyone!

I would like to extract unique records based on a condition. For =
example, how to extract unique record from column 'B' when column 'A' =
has "AP" or any other desired condition.

The data is as follows:

      A  B =20
      MI 70056542=20
      MI 70056543=20
      AP PATRICK CUDAHY INCORPORATED=20
      AP PATRICK CUDAHY INCORPORATED=20
      AP SUGAR CREEK PACKING CO=20
      AP SUGAR CREEK PACKING CO=20
      AP VICTORYS KITCHEN=20
      AP VICTORYS KITCHEN=20
      AP BRIGHT CHEESE HOUSE=20
      AP BRIGHT CHEESE HOUSE=20
      AP CAPPOLA FOODS INC=20


The final result should look like this:

      C =20
      PATRICK CUDAHY INCORPORATED=20
      SUGAR CREEK PACKING CO=20
      VICTORYS KITCHEN=20
      BRIGHT CHEESE HOUSE=20
      CAPPOLA FOODS INC=20


Thanks=20
SJ
------=_NextPart_000_0012_01C781BF.08FB92F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3059" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Arial Narrow" size=3D2>Hello everyone!</FONT></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2>I would like to extract unique =
records=20
based on a condition. For example, how to extract unique record from =
column 'B'=20
when column 'A' has "AP" or any other desired condition.</FONT></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2>The data is as =
follows:</FONT></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV>
<TABLE style=3D"WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=3D0 =

cellPadding=3D0 width=3D217 border=3D0 x:str>
  <COLGROUP>
  <COL style=3D"WIDTH: 16pt; mso-width-source: userset; mso-width-alt: =
672"=20
  width=3D21>
  <COL style=3D"WIDTH: 147pt; mso-width-source: userset; mso-width-alt: =
6272"=20
  width=3D196>
  <TBODY>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl25=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: =
16pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
    width=3D21 height=3D17 x:str=3D"A "><STRONG><FONT size=3D2><FONT=20
      face=3D"Arial Narrow">A<SPAN=20
      style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD>
    <TD class=3Dxl25=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 147pt; =
BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"=20
    width=3D196 x:str=3D"B "><STRONG><FONT size=3D2><FONT face=3D"Arial =
Narrow">B<SPAN=20
      style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>MI</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"=20
    align=3Dright x:num><FONT face=3D"Arial Narrow" =
size=3D2>70056542</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>MI</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"=20
    align=3Dright x:num><FONT face=3D"Arial Narrow" =
size=3D2>70056543</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY =
INCORPORATED</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY =
INCORPORATED</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>SUGAR CREEK PACKING =
CO</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>SUGAR CREEK PACKING =
CO</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>VICTORYS KITCHEN</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>VICTORYS KITCHEN</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE =
HOUSE</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE =
HOUSE</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
      face=3D"Arial Narrow" size=3D2>CAPPOLA FOODS=20
INC</FONT></TD></TR></TBODY></TABLE></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2>The final result should look =
like=20
this:</FONT></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV>
<TABLE style=3D"WIDTH: 147pt; BORDER-COLLAPSE: collapse" cellSpacing=3D0 =

cellPadding=3D0 width=3D196 border=3D0 x:str>
  <COLGROUP>
  <COL style=3D"WIDTH: 147pt; mso-width-source: userset; mso-width-alt: =
6272"=20
  width=3D196>
  <TBODY>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl25=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: =
147pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
    width=3D196 height=3D17 x:str=3D"C "><STRONG><FONT size=3D2><FONT=20
      face=3D"Arial Narrow">C<SPAN=20
      style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY=20
      INCORPORATED</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>SUGAR CREEK PACKING =

  CO</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>VICTORYS =
KITCHEN</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE=20
HOUSE</FONT></TD></TR>
  <TR style=3D"HEIGHT: 12.75pt" height=3D17>
    <TD class=3Dxl24=20
    style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
    height=3D17><FONT face=3D"Arial Narrow" size=3D2>CAPPOLA FOODS=20
  INC</FONT></TD></TR></TBODY></TABLE></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2>Thanks&nbsp;</FONT></DIV>
<DIV><FONT face=3D"Arial Narrow" size=3D2>SJ</FONT></DIV></BODY></HTML>

------=_NextPart_000_0012_01C781BF.08FB92F0--

0
jameel68 (50)
4/18/2007 5:39:49 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
607 Views

Similar Articles

[PageSpeed] 6

I'd do that with a Pivot Table.  

This should help

http://www.cpearson.com/excel/pivots.htm

"SSJ" wrote:

> Hello everyone!
> 
> I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition.
> 
> The data is as follows:
> 
>       A  B  
>       MI 70056542 
>       MI 70056543 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP SUGAR CREEK PACKING CO 
>       AP SUGAR CREEK PACKING CO 
>       AP VICTORYS KITCHEN 
>       AP VICTORYS KITCHEN 
>       AP BRIGHT CHEESE HOUSE 
>       AP BRIGHT CHEESE HOUSE 
>       AP CAPPOLA FOODS INC 
> 
> 
> The final result should look like this:
> 
>       C  
>       PATRICK CUDAHY INCORPORATED 
>       SUGAR CREEK PACKING CO 
>       VICTORYS KITCHEN 
>       BRIGHT CHEESE HOUSE 
>       CAPPOLA FOODS INC 
> 
> 
> Thanks 
> SJ
0
4/18/2007 5:46:32 PM
You could use Excel's advanced filter functionality.  Follow the menu path 
Data / Filter / Advanced filter.  Fill out the appropriate information and 
ensure to check the unique records only box.  Look up Advanced Filter on 
Excel help.

Hope that gives you a start.

"SSJ" wrote:

> Hello everyone!
> 
> I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition.
> 
> The data is as follows:
> 
>       A  B  
>       MI 70056542 
>       MI 70056543 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP SUGAR CREEK PACKING CO 
>       AP SUGAR CREEK PACKING CO 
>       AP VICTORYS KITCHEN 
>       AP VICTORYS KITCHEN 
>       AP BRIGHT CHEESE HOUSE 
>       AP BRIGHT CHEESE HOUSE 
>       AP CAPPOLA FOODS INC 
> 
> 
> The final result should look like this:
> 
>       C  
>       PATRICK CUDAHY INCORPORATED 
>       SUGAR CREEK PACKING CO 
>       VICTORYS KITCHEN 
>       BRIGHT CHEESE HOUSE 
>       CAPPOLA FOODS INC 
> 
> 
> Thanks 
> SJ
0
4/18/2007 5:52:03 PM
Here's a way doing it with advanced filter, assume the data starts in A4 
with a header, in for instance G2 put

=AND(A5="AP",COUNTIF($B$5:B5,B5)=1)

leave G1 blank, then apply the filter, select $G$1:$G$2 as the criteria 
range and select copy to another location


-- 
Regards,

Peo Sjoblom

"SSJ" <jameel68@yahoo.com> wrote in message 
news:%23Lq5RCegHHA.4188@TK2MSFTNGP02.phx.gbl...
Hello everyone!

I would like to extract unique records based on a condition. For example, 
how to extract unique record from column 'B' when column 'A' has "AP" or any 
other desired condition.

The data is as follows:

      A  B
      MI 70056542
      MI 70056543
      AP PATRICK CUDAHY INCORPORATED
      AP PATRICK CUDAHY INCORPORATED
      AP SUGAR CREEK PACKING CO
      AP SUGAR CREEK PACKING CO
      AP VICTORYS KITCHEN
      AP VICTORYS KITCHEN
      AP BRIGHT CHEESE HOUSE
      AP BRIGHT CHEESE HOUSE
      AP CAPPOLA FOODS INC


The final result should look like this:

      C
      PATRICK CUDAHY INCORPORATED
      SUGAR CREEK PACKING CO
      VICTORYS KITCHEN
      BRIGHT CHEESE HOUSE
      CAPPOLA FOODS INC


Thanks
SJ 


0
terre081 (3244)
4/18/2007 6:14:07 PM
This is a multi-part message in MIME format.

------=_NextPart_000_0016_01C781C4.49AEE400
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Try this:

Assume the range of data is A2:B21

Enter this array** formula in C2 and copy down until you get #NUM! =
errors meaning all uniques have been extracted:

=3DINDEX(B$2:B$21,SMALL(IF(A$2:A$21=3D"AP",IF(B$2:B$21<>"",IF(MATCH(A$2:A=
$21&B$2:B$21,A$2:A$21&B$2:B$21,0)=3DROW(B$2:B$21)-ROW(B$2)+1,ROW(B$2:B$21=
)-ROW(B$2)+1))),ROWS($1:1)))

** array formulas *MUST* be entered using the key combination of =
CTRL,SHIFT,ENTER (not just ENTER)

Biff
  "SSJ" <jameel68@yahoo.com> wrote in message =
news:%23Lq5RCegHHA.4188@TK2MSFTNGP02.phx.gbl...
  Hello everyone!

  I would like to extract unique records based on a condition. For =
example, how to extract unique record from column 'B' when column 'A' =
has "AP" or any other desired condition.

  The data is as follows:

        A  B =20
        MI 70056542=20
        MI 70056543=20
        AP PATRICK CUDAHY INCORPORATED=20
        AP PATRICK CUDAHY INCORPORATED=20
        AP SUGAR CREEK PACKING CO=20
        AP SUGAR CREEK PACKING CO=20
        AP VICTORYS KITCHEN=20
        AP VICTORYS KITCHEN=20
        AP BRIGHT CHEESE HOUSE=20
        AP BRIGHT CHEESE HOUSE=20
        AP CAPPOLA FOODS INC=20


  The final result should look like this:

        C =20
        PATRICK CUDAHY INCORPORATED=20
        SUGAR CREEK PACKING CO=20
        VICTORYS KITCHEN=20
        BRIGHT CHEESE HOUSE=20
        CAPPOLA FOODS INC=20


  Thanks=20
  SJ
------=_NextPart_000_0016_01C781C4.49AEE400
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.3059" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Try this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Assume the range of data is =
A2:B21</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Enter this array** formula in C2 and =
copy down=20
until you get #NUM! errors meaning all uniques have been =
extracted:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>=3DINDEX(B$2:B$21,SMALL(IF(A$2:A$21=3D"AP",IF(B$2:B$21&lt;&gt;""=
,IF(MATCH(A$2:A$21&amp;B$2:B$21,A$2:A$21&amp;B$2:B$21,0)=3DROW(B$2:B$21)-=
ROW(B$2)+1,ROW(B$2:B$21)-ROW(B$2)+1))),ROWS($1:1)))</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>** array formulas *MUST* be entered =
using the key=20
combination of CTRL,SHIFT,ENTER (not just ENTER)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Biff</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"SSJ" &lt;<A =
href=3D"mailto:jameel68@yahoo.com">jameel68@yahoo.com</A>&gt;=20
  wrote in message <A=20
  =
href=3D"news:%23Lq5RCegHHA.4188@TK2MSFTNGP02.phx.gbl">news:%23Lq5RCegHHA.=
4188@TK2MSFTNGP02.phx.gbl</A>...</DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2>Hello everyone!</FONT></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2>I would like to extract =
unique records=20
  based on a condition. For example, how to extract unique record from =
column=20
  'B' when column 'A' has "AP" or any other desired =
condition.</FONT></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2>The data is as =
follows:</FONT></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV>
  <TABLE style=3D"WIDTH: 163pt; BORDER-COLLAPSE: collapse" =
cellSpacing=3D0=20
  cellPadding=3D0 width=3D217 border=3D0 x:str>
    <COLGROUP>
    <COL style=3D"WIDTH: 16pt; mso-width-source: userset; mso-width-alt: =
672"=20
    width=3D21>
    <COL style=3D"WIDTH: 147pt; mso-width-source: userset; =
mso-width-alt: 6272"=20
    width=3D196>
    <TBODY>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl25=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: =
16pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
      width=3D21 height=3D17 x:str=3D"A "><STRONG><FONT size=3D2><FONT=20
        face=3D"Arial Narrow">A<SPAN=20
        style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD>
      <TD class=3Dxl25=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 147pt; =
BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"=20
      width=3D196 x:str=3D"B "><STRONG><FONT size=3D2><FONT=20
        face=3D"Arial Narrow">B<SPAN=20
        style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>MI</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"=20
      align=3Dright x:num><FONT face=3D"Arial Narrow" =
size=3D2>70056542</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>MI</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"=20
      align=3Dright x:num><FONT face=3D"Arial Narrow" =
size=3D2>70056543</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY =
INCORPORATED</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY =
INCORPORATED</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>SUGAR CREEK PACKING =
CO</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>SUGAR CREEK PACKING =
CO</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>VICTORYS KITCHEN</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>VICTORYS KITCHEN</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE =
HOUSE</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE =
HOUSE</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>AP</FONT></TD>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt =
solid; BACKGROUND-COLOR: transparent"><FONT=20
        face=3D"Arial Narrow" size=3D2>CAPPOLA FOODS=20
  INC</FONT></TD></TR></TBODY></TABLE></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2>The final result should look =
like=20
  this:</FONT></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV>
  <TABLE style=3D"WIDTH: 147pt; BORDER-COLLAPSE: collapse" =
cellSpacing=3D0=20
  cellPadding=3D0 width=3D196 border=3D0 x:str>
    <COLGROUP>
    <COL style=3D"WIDTH: 147pt; mso-width-source: userset; =
mso-width-alt: 6272"=20
    width=3D196>
    <TBODY>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl25=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: =
147pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
      width=3D196 height=3D17 x:str=3D"C "><STRONG><FONT size=3D2><FONT=20
        face=3D"Arial Narrow">C<SPAN=20
        style=3D"mso-spacerun: =
yes">&nbsp;</SPAN></FONT></FONT></STRONG></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>PATRICK CUDAHY=20
        INCORPORATED</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>SUGAR CREEK =
PACKING=20
      CO</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>VICTORYS =
KITCHEN</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>BRIGHT CHEESE=20
    HOUSE</FONT></TD></TR>
    <TR style=3D"HEIGHT: 12.75pt" height=3D17>
      <TD class=3Dxl24=20
      style=3D"BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: =
windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: =
windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"=20
      height=3D17><FONT face=3D"Arial Narrow" size=3D2>CAPPOLA FOODS=20
    INC</FONT></TD></TR></TBODY></TABLE></DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3D"Arial Narrow" size=3D2>Thanks&nbsp;</FONT></DIV>
  <DIV><FONT face=3D"Arial Narrow" =
size=3D2>SJ</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0016_01C781C4.49AEE400--

0
biffinpitt (3172)
4/18/2007 6:17:25 PM
Try this:

=IF(ISERR(SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))),ROWS($1:1))),"",INDEX($B$2:$B$12,SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy as far as needed


"SSJ" wrote:

> Hello everyone!
> 
> I would like to extract unique records based on a condition. For example, how to extract unique record from column 'B' when column 'A' has "AP" or any other desired condition.
> 
> The data is as follows:
> 
>       A  B  
>       MI 70056542 
>       MI 70056543 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP PATRICK CUDAHY INCORPORATED 
>       AP SUGAR CREEK PACKING CO 
>       AP SUGAR CREEK PACKING CO 
>       AP VICTORYS KITCHEN 
>       AP VICTORYS KITCHEN 
>       AP BRIGHT CHEESE HOUSE 
>       AP BRIGHT CHEESE HOUSE 
>       AP CAPPOLA FOODS INC 
> 
> 
> The final result should look like this:
> 
>       C  
>       PATRICK CUDAHY INCORPORATED 
>       SUGAR CREEK PACKING CO 
>       VICTORYS KITCHEN 
>       BRIGHT CHEESE HOUSE 
>       CAPPOLA FOODS INC 
> 
> 
> Thanks 
> SJ
0
4/19/2007 2:52:04 AM
Yuck!

You'll be doing yourself (and others) a great benefit by dropping your use 
of INDIRECT so much. It isn't efficient at all and it's not necessary.

With a formula like that it's better to not use an error trap and just use 
conditional formatting to hide them. Or, use a helper cell to get the count 
of uniques that meet the criteria and then refer to that cell like this:

=IF(ROWS($1:1)<=A$1,INDEX(..........................),"")

It's more efficient and makes the formula smaller.

Using your formula as is, is nearly 7.5 times slower (on average) on a 
recalculation (when the workbook calculates)

Biff

"Teethless mama" <Teethlessmama@discussions.microsoft.com> wrote in message 
news:953621DB-5917-48E7-93C1-FC2C2BD5EB02@microsoft.com...
> Try this:
>
> =IF(ISERR(SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))),ROWS($1:1))),"",INDEX($B$2:$B$12,SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))),ROWS($1:1))))
>
> ctrl+shift+enter, not just enter
> Drag the Fill Handle to copy as far as needed
>
>
> "SSJ" wrote:
>
>> Hello everyone!
>>
>> I would like to extract unique records based on a condition. For example, 
>> how to extract unique record from column 'B' when column 'A' has "AP" or 
>> any other desired condition.
>>
>> The data is as follows:
>>
>>       A  B
>>       MI 70056542
>>       MI 70056543
>>       AP PATRICK CUDAHY INCORPORATED
>>       AP PATRICK CUDAHY INCORPORATED
>>       AP SUGAR CREEK PACKING CO
>>       AP SUGAR CREEK PACKING CO
>>       AP VICTORYS KITCHEN
>>       AP VICTORYS KITCHEN
>>       AP BRIGHT CHEESE HOUSE
>>       AP BRIGHT CHEESE HOUSE
>>       AP CAPPOLA FOODS INC
>>
>>
>> The final result should look like this:
>>
>>       C
>>       PATRICK CUDAHY INCORPORATED
>>       SUGAR CREEK PACKING CO
>>       VICTORYS KITCHEN
>>       BRIGHT CHEESE HOUSE
>>       CAPPOLA FOODS INC
>>
>>
>> Thanks
>> SJ 


0
biffinpitt (3172)
4/19/2007 3:53:26 AM
Reply:

Similar Artilces: