Bring in all matched unique values using lookup

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20
Hello,
I have the following data in sheet1.
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>State</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>County</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Kings</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Kings</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Queens</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Bronx</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Queens</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Bronx</TD></TR></TBODY></TABLE>

I need to bring in all uniqe county associated with State. In that case, NY will have county Kings, Queens and Bronx. My table in sheet2 should look like below:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>State</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>County</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NY</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Kings</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Queens</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Bronx</TD></TR></TBODY></TABLE>

I know how to bring in all matched value but I don't know how to only bring in uniqe matched value.

thanks!
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Hi, I've been working on the virtually the SAME PROBLEM!

Would it be a problem if you insert another column for full automation?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Hi matrix7410,

No problem. Assume your formula produces an error message.
Excel 2003
You must first enclose your formula with the =iserror function. Ex. =iserror(your formula). If your formula already produces an error message, using the =iserror function will produce a true display. Then use the =if statement to able to display something showing the formula is true or fase. Put this =if function on the outside of your formula. Ex: =if(iserror(your formula shown have an error message),"","whatever") Since your formula has an error message. The iserror part will give a true answer. Basically, the =if(formula,"result if true","result if false") So your formula becomes =if(iserror(your formula,"", "whatever"). The "" answers the true statement, giving a blank display. (I realize this explanation is wordy.)

Excel 2007 is much more simpler. Use only the =iferror function. Use this formula in enclose your formula. This function produces a specific result for formulas with error messages. Ex.
=iferror(formula,""). So this formula will leave a blank for error messages resulting from a formula.

Mike
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Matrix7410,
I hope this is much clearer. I took in account more than 1 state. I intentionally copied down to G10 and G11 to illustrate the Excel 2003 and Excel 2007 formulas to remove error messages. Excel 2007 is worth getting just for this very function! G10 and G11 now show blanks instead of error messages. Let me know if this helps.
Mike

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 117px"><COL style="WIDTH: 59px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD>State</TD><TD>County</TD><TD> </TD><TD>Total States</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD>NY</TD><TD>Kings</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD>NY</TD><TD>Kings</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD>NY</TD><TD>Queens</TD><TD> </TD><TD>Total NY Counties</TD><TD>NY</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD>NY</TD><TD>Bronx</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD>Kings</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD>NY</TD><TD>Queens</TD><TD> </TD><TD> </TD><TD>Queens</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD>NY</TD><TD>Bronx</TD><TD> </TD><TD> </TD><TD>Bronx</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD>NJ</TD><TD>Smith</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F4</TD><TD>{=SUM(IF(FREQUENCY(IF(C4:C10<>"",MATCH($C$4:$C$10,$C$4:$C$10,0)),ROW($C$4:$C$10)-ROW($C$4)+1)>0,1))}</TD></TR><TR><TD>F7</TD><TD>{=SUM(IF(FREQUENCY(IF(D4:D10<>"",IF($C$4:$C$10=$C$4,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1)>0,1))}</TD></TR><TR><TD>G7</TD><TD>{=INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G7)))}</TD></TR><TR><TD>G8</TD><TD>{=INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G8)))}</TD></TR><TR><TD>G9</TD><TD>{=INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G9)))}</TD></TR><TR><TD>G10</TD><TD>=IF(ISERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10)))),"",INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10))))</TD></TR><TR><TD>G11</TD><TD>=IFERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G11))),"")</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
 

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20
Hi Mike,
In G10, your iserror function works but not the if(iserror()) function. For some reason if function is not wrapping the iserror function. The below function works.

{=ISERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10))))}

But the below function does not.

{=if(ISERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10)))),"","It works")}
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

Hi Mike,
In G10, your iserror function works but not the if(iserror()) function. For some reason if function is not wrapping the iserror function. The below function works.

{=ISERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10))))}

But the below function does not.

{=if(ISERROR(INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH($D$4:$D$10,$D$4:$D$10,0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G10)))),"","It works")}

Taking up the exhibit already posted above...

<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=406><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5347" width=150><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2409374 class=xl63 height=19 width=64>State</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>County</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150>Total States</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Kings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Kings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Queens</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150>Total NY Counties</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>NY</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Bronx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Kings</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Queens</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Queens</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Bronx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Bronx</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>NJ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 113pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=150> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2409374 class=xl64> </TD></TR></TBODY></TABLE>

Try...

F4, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(C4:C10<>"",MATCH("~"&C4:C10,C4:C10&"",0)),ROW(C4:C10)-ROW(C4)+1),1))

F7, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(D4:D10<>"",IF(C4:C10=G6,MATCH("~"&D4:D10,D4:D10&"",0))),ROW(D4:D10)-ROW(D4)+1),1))

G7, control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$7:G7)<=$F$7,INDEX($D$4:$D$10,SMALL(IF(FREQUENCY(IF($D$4:$D$10<>"",IF($C$4:$C$10=$G$6,MATCH("~"&$D$4:$D$10,$D$4:$D$10&"",0))),ROW($D$4:$D$10)-ROW($D$4)+1),ROW($D$4:$D$10)-ROW($D$4)+1),ROWS($G$7:G7))),"")

If you want to use the list created in column D in data validation:

Let Sheet1 house the data.

Define NY (using Insert|Name|Define) as referring to:

=OFFSET(Sheet1!$G$7,0,0,Sheet1!$F$7)
 

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20
Thanks! Aladdin, that actually worked great.

I'm still kind of puzzled why I couldn't get the Mike's if solution to work.

Mike, you sir is the best and my deepest gratitude for working with me and helping me on this for days.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

Thanks! Aladdin, that actually worked great.

I'm still kind of puzzled why I couldn't get the Mike's if solution to work.

Mike, you sir is the best and my deepest gratitude for working with me and helping me on this for days.

There is no need to run ISERROR (or IFERROR) with this formula system for composing a unique list. On older Excel versions, applying ISERROR would run up against the limit for "nesting levels" and also necessitate "calculating twice the same thing".
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Hi matrix,
Check all your parentheses. It should work. Remember, this cell is already producing an error message. The =iserror(formula) will produce a true result. So it becomes =if(formula that is true,"result if true","result if false"). So =if(formula,"",formula) should show blank, answering the true result. It is irrelevant as to what you put in "result if true" argument. It will only answer what is in "result if true."

Select cell of formula. Hit F2, highlight all the iserror(formula) portion with your mouse. Then hit F9, it will show =if(true,"",formula).

Mike Szczesny
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Matrix,

By all means refer to Aladin. He is one of the very best when it comes to Excel issues. I am still learning about all the many nuances of these complicated formulas.
Mike Szczesny
 

Watch MrExcel Video

Forum statistics

Threads
1,108,855
Messages
5,525,227
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top