Hi all, I am wondering if it is possible to do a vlookup and instead of the result being the first value found that is returned, is there a way to return all the vallues associated to the look up? I have put an example on the atatchments (item 1).
Thanks,
Dean
Just to be clear, is it a VBA solution you are after?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
What is VBA?
Visual Basic for Applications (macros). I asked because I know that often people post in this section when in fact they are looking for a formula, not a macro.
VBA = Visual Basic for Applications
Basically, you need a macro to do this. The code for the macro (a User-Defined Function, called concatAll) has been mpasted into a module in your sheet.
Right click on the sheet name, then VIEW CODE then MODULE 1 to see it in place.
Please Login or Register to view this content.
The formula then needed is:
=concatall(IF($C$5:$C$12=C15,$D$5:$D$12,""),CHAR(10))
This ia an array formula:
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
The CHAR(10) bit starts a new line after every result.
Enable macros on opening this sheet.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Sorry didnt see this was a macro forum, i'll post to the correct area
Thanks Glenn,
this is a bit out of my abilities I think, thanks anyway.
Dean
No need to start again - I have moved the thread.
If you want the answers in the same cell.... you have no choice.
Glenn,
have i got this correct, "paste all the code into the code sheet and return to the excel sheet"
Paste formula
CTRL+SHIFT+ENTER
???
Dean
Great got it - Thanks loads
If you are wanting to copy this into your real sheet...
right click ion sheet name, VIEW code/Insert module
copy in the code, save as macro enabled. use formula, with the corrected ranges, enter with CTRL-SHIFT-ENTEr and not just enter.
Hi Glenn,
Thanks for posting concatAll - Not come across it before!
This bit isn't working for me - perhaps a version issue?The CHAR(10) bit starts a new line after every result.
Are you able to change the new line to ", ", after each result?
Thanks in advance
(Apologises for poaching thread Deanblew)
SharpL - unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member - start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Nobody should respond to SharpL's query in this thread, please. Thanks!
Apologises AliGW.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks