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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi Matriz7410,

Try this. The frequency formula is very useful. However, this formula may be intimidating to understand. Assume NY is in A2:A6. The county name are in B2:b6. First of all the formula to calculate total number of uniqiue counties is in C2: =sum(if(frequency(if($b$2:$b$6<>"",match($b$2:$b$6,$b$2:$6,0)),row($b$2:$b$6)-row($b$2)+1)>0,1)). Use C-S-E. This will give an answer of 3. In D2: =index($b$2:$b$6),small(if(frequency(if($b$2:$b$6<>"",match($b$2:$b$6,$b$2:$b$6,0)),row($b$2:$b$6)-row($b$2)+1),row($b$2:$b$6)-row($b$2)+1),rows($d$2:d2))) Use C-S-E copy down. You should see 2 counties, Kings, Queens, and Bronx.

If you are only working with numbers,you can exclude the range<>"" and match portion of your formula. Therefore the total unique quantites would be =sumproduct(--(frequency(b2:b6,b2:b6)>0)). In E2: Finding the unique items would be =index(range,small(if(frequency($b$2:$b$6,$b$2:$b$6),row($b$2:$b$6-row($b$2)+1),rows($e$2:e2))). Use C-S-E. copy down

HTH,
Mike Szczesny
 
Upvote 0
Mike, I really appreciate your help and your forumula works great if I only have one state but I have more than one state. I apologize for not putting it in my dummy table.

Let me just explain again just to clarify even more. Basically, what I really want to do is have a drop down listing of all 50 states where the user will pick a state and than you will see the listings of all unique county pop up
in the following manner.


State: NY (drop down option)


County:
Kings
Queens
Bronx

State: NJ
County:
Essex
Bergen

My dummy table looks like the following
State County
NY Kings
NY Kings
NY Queens
NY Kings
NY Bronx
NJ Essex
NJ Bergen
NJ Essex
NY Kings
FL Miami
NJ Essex

thanks!
 
Upvote 0
Hi matrix7410,
That should not be a problem. All you would have to do is insert this additonal =if(range=county into your formula. I would put it here: if(range<>"",if(range=county,match(.......... Just insert an additional )to the ))after the match function.As you put more if(statements, you need to add more )'s after the match function.

HTH
Mike
 
Upvote 0
It works very nice! thank you! I did see some error sign when I copied it down and there were no more unique match. Any way I can eliminate those error sign by using iserror function?

<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=146 border=0 x:str><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 110pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=146 height=17>County

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Kings</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Queens</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bronx</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 id=td_post_2407552 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:err="#NUM!">#NUM!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:err="#NUM!">#NUM!</TD></TR></TBODY></TABLE>
 
Upvote 0
Excel Workbook
ABCDEF
2NYqueens3queens3queens
3NYstatton islandstatton islandstatton island
4NYbrooklynbrooklynbrooklyn
5NYbrooklyn#NUM!
6NYbrooklyn#NUM!
7NYqueens#NUM!
8NYstatton island#NUM!
Sheet4
Excel Workbook
E
23
Sheet4


After getting the unique count, try using this formula copied down. it will show a blank when you pass the count of unique
 
Upvote 0
Since you do not find any more unique items, then you should get error messages. You can just remove these. Or you can do this. If you have Excel 2003, you can use the =iserror(function and the =if(function. If your formula already is a error, the iserror function will produce true.

=iserror(your formula already an error) produces true. Then add =if(iserror(your formula),"","a"). If true, the error messages is replaced by the blank.

If Excel 2007, it is much easier. The =iferror( function already does this.
=iferror(your formula,"") will produce a blank to remove the error message.
Mike
 
Upvote 0
Sorry Mike, I'm little confused as to where to plug in the if(ieserror()) function. I played around with bunch of formations, but nothing works. Here is my formula: Col B is County, Col A is State, Col D is drop down value.

=INDEX($B$2:$B$11,SMALL(IF(FREQUENCY(IF($B$2:$B$11<>"",IF($A$2:$A$11<>"",IF($A$2:$A$11=$D$2,MATCH($B$2:$B$11,$B$2:$B$11,0)))),ROW($B$2:$B$11)-ROW($B$2)+1),ROW($B$2:$B$11)-ROW($B$2)+1),ROWS($C$2:C2)))
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top