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!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20
I need to automate it and would like to stay away from pivot if possible.

thanks!
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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
 

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20

ADVERTISEMENT

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!
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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
 

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20

ADVERTISEMENT

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>
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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
 

matrix7410

New Member
Joined
Jan 25, 2010
Messages
20
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,257
Messages
5,527,658
Members
409,780
Latest member
Sudheer121

This Week's Hot Topics

Top