Can I get MultiLookup with Multiple Unique Values

**e4excel** · 01-06-2011, 03:01 AM

Can I get MultiLookup with Multiple Unique Values

I am using the traditional MultiLookup Formula to get Multiple Values:

=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=$B$2,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2)),1)

However there are duplicate values which I need to avoid so is there a possiblity of getting this done..

I am oK with Arrays Non-Arrays.

Unfortuantely due to sensitive information a file cannot be added unless absolutely neccessary however please find the below info to understand the requirement..

Col A--------Col B
Banks------Unique Dates
Bank A----1-Jan-2010
Bank A----1-Jan-2010
Bank B----1-Feb-2010
Bank C----1-Jan-2010

As can be seen in the above range, as my Lookup is 1-Jan-2010
I want the result with only set of Bank Names i.e. Bank A only once
Bank A
Bank C

Regards
E

**DonkeyOte** · 01-06-2011, 04:52 AM

@e4excel - you appear to have quite a few similar threads on the go and there is little in your thread titles to distinguish between them.

Originally Posted by e4excel

Unfortuantely due to sensitive information a file cannot be added unless absolutely necessary

AFAIK there is nothing to prevent you from create a "mock-up" file that mimics both data types & layout ... and which contains both "before" and "after"

Originally Posted by e4excel

As can be seen in the above range, as my Lookup is 1-Jan-2010
I want the result with only set of Bank Names i.e. Bank A only once

In reality this is not clear from your sample. At all.

Why not just use a Pivot Table ?

**e4excel** · 01-06-2011, 05:06 AM

Originally Posted by DonkeyOte

@e4excel - you appear to have quite a few similar threads on the go and there is little in your thread titles to distinguish between them.

AFAIK there is nothing to prevent you from create a "mock-up" file that mimics both data types & layout ... and which contains both "before" and "after"

Why not just use a Pivot Table ?

Yeah, its similat but not the same as there is the MultiLookup concept common in both or most of my recent threads, however the only difference is that I need to have only Unique Values unlike all values...As these values like the result is going to forma Dropdown in other files.

Bank A
Bank A
Bank C

and therefore I am trying to get only the Unique Values..
Bank A
Bank C

AFAIK ????? I didnt understand this..

I am making the file as the original one cant be attached as it has all personal financial information..

I am felling very sheepish but I relly dont use PIVOTs and VBA stuff much but can try it though but I dont need any Sub-TOtals or any results but just the values..

Regards
E

**zbor** · 01-06-2011, 05:14 AM

Maybe DO can fix this

Here is file that in column F has his solution...

I've tryied to combine them with column B (in column G) but wo success.... Can't see where it go wrong....

(AFAIK: As Fas As I Know)

**DonkeyOte** · 01-06-2011, 05:30 AM

If you only intend to do this for one date at a time a Pivot seems sensible (UniqueDates As Page Field / Report Filter and Banks as Row Field / Row Label)

If that's not reality - then using the sample - again I would insert a blank cell above E1 to hold count of unique "banks" on specified day:

Please Login or Register  to view this content.

then in terms of retrieving the banks themselves:

Please Login or Register  to view this content.

Modify ranges as appropriate.

**e4excel** · 01-06-2011, 06:32 AM

Dear DO and Zbor,

I had given the references in Col A and Col B but sorry to say in the dummy file the refrences are different so please excuse me for that..

See the attachement..

ANd thanks for AFAIK: As Fas As I Know)

NIKT - Now I know that!

Regards
E

**DonkeyOte** · 01-06-2011, 06:38 AM

below

Please Login or Register  to view this content.

**e4excel** · 01-06-2011, 07:16 AM

Dear DO,

Thanks a lot, Your code was superb I was having extra columns to get the same but in vain..

Had I got the code in the Column G right then maybe there would have been something I could do..

Just for FYI still..
COL G1 -->Unique Bank Count

Please Login or Register  to view this content.

I didnt realise but what I wanted was to get each Bank assigned with a Unique No repeated the entire column immaterial of the arrangement of the Bank Names..

The query is SOLVED and thanks a lot for all the help provided..

**e4excel** · 10-10-2011, 06:52 AM

Originally Posted by DonkeyOte

If you only intend to do this for one date at a time a Pivot seems sensible (UniqueDates As Page Field / Report Filter and Banks as Row Field / Row Label)

If that's not reality - then using the sample - again I would insert a blank cell above E1 to hold count of unique "banks" on specified day:

Please Login or Register  to view this content.

then in terms of retrieving the banks themselves:

Please Login or Register  to view this content.

Modify ranges as appropriate.

Dear DO,

I am trying to use this concept in another file but am not getting the correct answers and the problem is that I ma getting some answers in decimals..

I am sorry to ask again in this CLODSED thread as this is exactly the same question..

At work I am not bale to upload the file too..!

But just based on the concept I can explain the query...

I have just 2 X Columns A and B which have 2 Multiple City Names in the Col A and the Col B has Alphabets like "A","B","C" etc Muliple Times..

I just need to get a Unique List of these Alphabets for each city..

I tried the formula first for getting the Unique Count where I am getting different values..

The Col A contain all City Names such as "Mumbai","Thane","Navi-Mumbai" and "Pune"..

I tried to use the formula :

Please Login or Register  to view this content.

The cell K2 contains a Drop-Down with the 4 City values..

**e4excel** · 10-11-2011, 01:08 AM

Dear DO/Forum,

Please find the data below:
Col A-------Col B
City Alphabet
Mumbai A
Mumbai A
Mumbai A
Mumbai A
Mumbai A
Mumbai A
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai B
Mumbai C
Mumbai C
Mumbai C
Mumbai C
Mumbai C
Mumbai C
Mumbai D
Mumbai D
Mumbai D
Mumbai F
Mumbai G
Mumbai G
Mumbai G
Mumbai G
Mumbai G
Mumbai H
Mumbai I
Mumbai J
Mumbai J
Mumbai J
Mumbai J
Mumbai J
Mumbai K
Mumbai K
Mumbai K
Mumbai K
Mumbai K
Mumbai K
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai M
Mumbai N
Mumbai N
Mumbai N
Mumbai P
Mumbai P
Mumbai P
Mumbai P
Mumbai R
Mumbai R
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai S
Mumbai T
Mumbai T
Mumbai T
Mumbai T
Mumbai T
Mumbai V
Mumbai V
Mumbai V
Mumbai V
Mumbai V
Mumbai V
Mumbai W
Mumbai W
Navi Mumbai A
Navi Mumbai B
Navi Mumbai G
Navi Mumbai J
Navi Mumbai K
Navi Mumbai K
Navi Mumbai M
Navi Mumbai N
Navi Mumbai N
Navi Mumbai T
Navi Mumbai U
Navi Mumbai V
Thane A
Thane A
Thane B
Thane B
Thane B
Thane B
Thane B
Thane B
Thane B
Thane C
Thane D
Thane D
Thane D
Thane G
Thane G
Thane J
Thane J
Thane K
Thane K
Thane K
Thane K
Thane K
Thane K
Thane M
Thane M
Thane M
Thane M
Thane N
Thane N
Thane N
Thane P
Thane P
Thane S
Thane S
Thane S
Thane S
Thane T
Thane T
Thane T
Thane T
Thane T
Thane T
Thane U
Thane U
Thane V
Thane V
Thane V
Thane V
Thane V
Thane V
Thane W
Pune A
Pune A
Pune A
Pune A
Pune B
Pune B
Pune C
Pune C
Pune C
Pune D
Pune D
Pune D
Pune D
Pune D
Pune E
Pune G
Pune H
Pune H
Pune I
Pune I
Pune K
Pune K
Pune K
Pune K
Pune K
Pune K
Pune L
Pune M
Pune M
Pune M
Pune N
Pune N
Pune N
Pune N
Pune P
Pune P
Pune P
Pune P
Pune P
Pune P
Pune P
Pune R
Pune S
Pune S
Pune S
Pune S
Pune S
Pune S
Pune W
Pune W
Pune W
Pune Y

From the above data, I just need to get the Unique values for any city selected..
There are just 4 CITIES presently :

Mumbai
Navi Mumbai
Thane
Pune

I want to get a Unique List of the Aplhabets for any city selected as I want to create a Drop-Down of this Unique List...

Warm Regards
e4excel

**zbor** · 10-11-2011, 02:34 AM

e4excel: is it so hard to upload example workbook?

Look solution:

**e4excel** · 10-11-2011, 04:26 AM

Dear Forum,

Sincere Apologies for the inconvenience but from @work its not being allowed so easily and at home the net speed was very bad..

Thanks for the File Zbor..

But I need to get the Unique Alphabets for Each City..

Like if I select a City Mumbai then i need to get all the Alphabets next to Mumbai in the Column A but only once..
Finally, am able to upload a file now..

Hope this helps actually ..

Actually, I was trying to use the same concept which I had got from DonkeyOte , but in vain i was goofing up and therefore I needed help as this had already worked for me in the past in my other file..

Warm regards
e4excel

**zbor** · 10-11-2011, 09:24 AM

Do you mean this: http://www.contextures.com/xldataval02.html

**e4excel** · 10-13-2011, 01:39 AM

Dear Zbor,

I need help in getting only the Unique Alphabet selected for any city.
Example :

Mumbai :
A
B
C
D
F
G
H
I
J
K
M
N
P
R
S
T
V
W

Navi Mumbai
A
B
G
J
K
M
N
T
U
V

Pune
A
B
C
D
E
G
H
I
K
L
M
N
P
R
S
W
Y

Thane
A
B
C
D
G
J
K
M
N
P
S
T
U
V
W

So on selecting any city I just need to get the Alphabets which are present for that city and only UNIQUE VALUES.

Dear Zbor, I have been making the Data Validation based on that file, Also in the present scenario I have just made the list one below the other but if I had to make a random list then would i still be able to get the Alphabets List and also the Town List?

Warm Regards
e4excel

**e4excel** · 10-14-2011, 01:47 AM

The purpose of using this list is to get the Drop-Down List for Towns based on the Selected Cities for any Alphabets..

Now I was able to get the Towns based on the Alphabets for any city however, I thought of getting only the Unique List of Alphabets for the City so that it would be more prudent to get only those Alphabets as certain Cities have Fewer Alphabets for their Towns so thats the main requirement..

Presently I hve just arrnged the Data City-Wise one below the other however I also wanted to know If the data were to be arranged randomly then would this still work..

Regards
e4excel

**zbor** · 10-14-2011, 02:22 AM

Can you upload example?

**e4excel** · 10-14-2011, 04:38 AM

Dear Zbor,

I have Upl-loaded an Example to explain the exact requirement..

I need three Drop-Downs out of which one is already taken care of with your code and solution even in the Un-Arranged Data which is great and now the other Drop-Downs are to get the Unique Alphabets for the Selected City even if the Data is Un-Arranged if possible and then further geting the Towns List for that particular selected Alphabet and the Selected City...

Thanks in advance.

Warm Regards
e4excel

**e4excel** · 10-15-2011, 03:47 PM

Please can someone help me on this!

**e4excel** · 10-16-2011, 07:04 AM

Sorry Guys,

I was making a mistake in ignoring the Sr.NO and therefore I was getting an error the moment I realized the entire thing just un-puzzled itself..

Inconvenience caused is deeply regretted..

Thanks a lot for the Help again Zbor and not to forget DonkeyOtes help which actually helped me twice..

Warm Regards
e4excel

**e4excel** · 10-16-2011, 08:08 AM

Now that I have been able to get this done a new problem seems to surface which is rendering the program very slow..

After I got the Unique Alphabets for the City In the next Drop-down I needed to get the Subsequent Towns selected for any city for a particular Alphabet..

I used the INDIRECT() with a StartRng and EndRng it works well but it takes hell of a time to update the Drop-Down for Town after every change..

THe Formula used for the TOWN DROP-DOWN..

Please Login or Register  to view this content.

where the StrtRng is

Please Login or Register  to view this content.

where the EndRng is

Please Login or Register  to view this content.

This works but very slowly so please suggest iftheres a better way of getting the same result for the Alphabetic Towns for any city..

Warm Regards
e4excel

**zbor** · 10-16-2011, 08:22 AM

Yes, it will be slow. Both, INDIRECT and SUMPRODUCT are slow... You can try to minimize range so instead of whole A:A use minimum range as A2:A587

**e4excel** · 10-16-2011, 08:51 AM

Ok Zbor,

I shall do that I will use the last entry in the Column of the Pincodes Sheet + 1 to good effect in that case to get the exact range, however is there a better way of doing this rather than my approach..

As these ranges are so much in midde i thought of this method but if there is any other method then please advise as well and there was just one more thing I wanted to know is that in my Pincodes Sheet all my Lists are Sequentially Arranged but if I were to add it Randomly then what will be the Approach to get the same lIst?

Thanks in advance and also for the help provided..

Regards
e4excel

**e4excel** · 10-16-2011, 09:16 AM

Please Login or Register  to view this content.

How do I change this I tried to add the Indirect function with a Limited Range but I am getting an error?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Need help still..

**zbor** · 10-16-2011, 12:47 PM

I think at this point you should open new thread, focus on important part you need to solve and you can provide link to this post. Because it's too long and marked as Solved... So probably only I saw it and I'm confused with it...

Don't forget to put proper title and provide upload workbook...

**e4excel** · 10-17-2011, 06:26 AM

No Issues as You Say Zbor..

Will Start a New THREAD then..

Thanks for the assistance..Zbor..I did use the Unique Cities list with your help..

Warm regards
e4excel

**e4excel** · 02-09-2012, 10:42 AM

Just felt like adding something very valuable to this Query..!

https://sites.google.com/a/madrocket...te-single-list

This is by the Forum Guru Jerry Beaucaire's - Excel Assistant

Can I get MultiLookup with Multiple Unique Values

LinkBack

Thread Tools

Rate This Thread

Display

Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Bump NO Response

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Re: Can I get MultiLookup with Multiple Unique Values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions