Can I get MultiLookup with Multiple Unique Values

**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..

=INDIRECT("'PinCodes'!$D"&StrtRng&":$D"&StrtRng+EndRng-1)

where the StrtRng is

=MATCH('Booking Details'!$X3&"-"&'Booking Details'!$Y3,PinCodes!$B:$B&"-"&PinCodes!$C:$C,0)

where the EndRng is

=SUMPRODUCT((PinCodes!$B:$B='Booking Details'!$X3)*(PinCodes!$C:$C='Booking Details'!$Y3))

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

=SUMPRODUCT((PinCodes!$B:$B='Booking Details'!$X3)*(PinCodes!$C:$C='Booking Details'!$Y3))

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

=SUMPRODUCT((INDIRECT("'PinCodes!$B$2:$B$"&TotalTowns)='Booking Details'!$X3)*(INDIRECT("'PinCodes!$C$2:$C$"&TotalTowns)='Booking Details'!$Y3))

TotalTowns = Counta(PinCodes!$A:$A)

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, 09: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