Hi,
I'm using the Match/Index formulas to bring values from another sheet. I need to bring many values in the same row (different columns) and the only way I found until now is to index as many times as I need in different cells, which is quite complicated if there's too many columns (I need 15 values from different columns in the same row).
Is there any way I can create some sort of array, list or something like it to bring all the values all together in one cell?
Thank you!
Regards!
All sorts of ways to do this. but no-one can help you unless you provide us with something to work with. Please attach a sample sheet (NOT a screenshot), minus confidential information.
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
Attached you'll find 2 files. One with the list of items, the other with a code example I'm using.
The user types a Part Number, and the sheet finds the information in the list provided. What I would like, instead of typing the match/index formula in each column, is find the way to bring all the row information to one cell.
Thank you!
Is this what you meant? Drag right as far as you need...
But I don't wont them in different columns, I want all the columns in some sort of array or separated by commas in with cell
Misread your request.... I'll have a think.
There's a good concatenation UDF here:
https://www.excelforum.com/showthread.php?p=3096647
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Quite interesting, thanks for the reference. The UDF will be useful if I had all the columns and needed to concatenate them, is there any way I can get the match/index (or the vlookup) function to return an array? Otherwise, I should put everything in different columns and then use the function (I would be still be using lots of columns to then concatenate them).
Thank you!
I didn't download your files. I have a download size restriction.
I'm not sure if you're looking for something like this.
Data Range
We want to concatenate the entries from columns 2, 3 and 5 on the row that matches "A".
Using the concatall UDF...
Array entered**:
=concatall(INDEX(B2:F4,MATCH(A7,A2:A4,0),N(IF(1,{2,3,5}))),", ")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
If what you want is any more complicated than this then you're probably going to have to get someone to write a new UDF that is specific to your needs.
I doubt if tis will take you much further on... but you never know.
Have a look at the Post #8 in the following thread:-
http://www.excelforum.com/excel-form...e-formula.html
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
This one worked quite well.
"=concatall(INDEX(B2:F4,MATCH(A7,A2:A4,0),N(IF(1,{2,3,5}))),", ")"
I tried the same solution without the N formula, but didn't work, can you tell me what is the N formula doing?
Thank you!
In simple words as I think,
N(Array) is forcing Index Function to take its Column Argument as Array,
which means Index Function will return an array of values against what it usually does ie. returning a single value.
But I want to request Mr. Tony to have their say so that we can learn more of it.
Yes, that's what's happening.
Great then, the workaround is working perfectly (I brought 10 columns and have them in a comma separated cell). Thank you!
Two questions to follow up:
1) I'm using references to an external and quite big sheet (the price list). Is it normal that the size of my sheet (where I used the concatall) has increased to the same size as the price list? Is there anything I can do about it?
2) I also tried to build a UDF that sums all up, including the index/match, but doesn't work, probably because I'm using it wrong. Can you call excel functions from vba? is there a better way to access to price list from vba?
Thank you all for your help!
Sorry, I'm not much of a programmer.
Maybe try posting the question in the Excel Programming / VBA / Macros forum.
For Reducing File size..
You can try saving the workbook in *.xlsb format and see if there is any considerable difference.
For calling Excel General function in the vba,
you should know about Worksheetfunction Object.
and Evaluate Function as well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks