Can I get MultiLookup with Multiple Unique Values
I am using the traditional MultiLookup Formula to get Multiple Values:
However there are duplicate values which I need to avoid so is there a possiblity of getting this done..=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)
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
Last edited by e4excel; 01-06-2011 at 08:45 AM. Reason: Correction
@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"Originally Posted by e4excel
In reality this is not clear from your sample. At all.
Why not just use a Pivot Table ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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)
"Relax. What is mind? No matter. What is matter? Never mind!"
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:
then in terms of retrieving the banks themselves:E1: =SUMPRODUCT(($B$1:$B$10=$E2)/COUNTIF($A$1:$A$10,$A$1:$A$10&""))
Modify ranges as appropriate.E3: =IF(ROWS(E$3:E3)>E$1,"",INDEX($A$2:$A$10,MATCH(1,INDEX(($B$2:$B$10=E$2)*ISNA(MATCH($A$2:$A$10,E$2:E2,0)),0),0))) confirmed with Enter copied down to E11 (to cater for 9 possibilities given A2:A10)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
below
M1: =SUMPRODUCT(($F$2:$F$92=K2)*(MATCH($B$2:$B$92&"@"&$F$2:$F$92,$B$2:$B$92&"@"&$F$2:$F$92,0)=(ROW($B$2:$B$92)-ROW($B$2)+1))) confirmed with enter L2: =IF(ROWS(L$2:L2)>M$1,"",INDEX($B$2:$B$92,MATCH(1,INDEX(($F$2:$F$92=K$2)*ISNA(MATCH($B$2:$B$92,L$1:L1,0)),0),0))) confirmed with enter copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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..[ = IF($B2="","",IF(COUNTIF($B$1:$B2,$B2)<=1,IF(COUNTIF($B$2:$B2,$B2)>1,$G1,MAX($G$1:$G1)+1),$G1)) ]
The query is SOLVED and thanks a lot for all the help provided..
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 :
The cell K2 contains a Drop-Down with the 4 City values..=SUMPRODUCT(($A$2:$A$210=$K$2)/COUNTIF($B$2:$B$210,$B$2:$B$210&""))
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
e4excel: is it so hard to upload example workbook?
Look solution:
"Relax. What is mind? No matter. What is matter? Never mind!"
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
Last edited by e4excel; 10-14-2011 at 03:13 AM.
Do you mean this: http://www.contextures.com/xldataval02.html
"Relax. What is mind? No matter. What is matter? Never mind!"
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks