Hi All,
Can someone please explain me how I need to source multiple id's from one column into one cell, the identifier would be the author name.
Please see example attached.
Thanks in advanced.
Kind regards,
Nordin
Hi All,
Can someone please explain me how I need to source multiple id's from one column into one cell, the identifier would be the author name.
Please see example attached.
Thanks in advanced.
Kind regards,
Nordin
I think you need to post an example of what you want to see as a result.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Something like this one?
Done with PowerQuery (Get&Transform)
Hi JLGWhiz,
What I want to achieve is something like this
Paper Authors ID
Market Research Michael, Criss, David 1,2,3
Hi Sandy666,
That's exactly the outcome I am looking for but I can't see how you did this?
Data - Show Queries then double click on Table1 on the right side and again on the right side you'll see steps
I don't have that option under data??
here is:
showqueries.jpg
or you've Excel for students or something
or try Alt A PS
Last edited by sandy666; 02-16-2018 at 05:56 PM.
is it different in Excel for MAC??
you should update your profile to MAC 2016
less troubles and posts because Excel for Mac doesn't support (yet) PowerQuery
Thank you sorry did not know that there was a difference I will update my profile.
Looks much better
Have a nice day
One way:
How to install your new codePlease Login or Register to view this content.
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
Then use this array formula:
=concatall(IF(ISNUMBER(SEARCH(Authors!$B$2:$B$4,B2)),Authors!$A$2:$A$4,""),", ")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
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...
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
Here's your book with the code:
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Thank you xladept. I have closed the thread thanks to you I have the solution I am looking for.
You're welcome and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks