Great Excel trick once again. For anyone looking to create a single column or row array use the following dynamic array formula (can be entered with CSE or modified to do so): =TRIM(MID(SUBSTITUTE(A1, Delimiter, REPT(" ", LEN(A1))), (SEQUENCE(1, (LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1, 1, 1) - 1)*LEN(A1) + 1, LEN(A1))) A1 is the cell with the delimited list and Delimiter is the separator used (in Name Manager so it can be easily changed). SUBSTITUTE(A1, Delimiter, REPT(" ", LEN(A1))) --> delimited list (with delimiter replaced with spaces) (used for MID() formula text argument) (LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1 --> finds total number of items in list SEQUENCE(1, (LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1, 1, 1) --> finds item position in string (used for MID() formula start_num argument) LEN(A1) --> finds total number of characters in list string (used for MID() formula num_chars argument) MID() --> creates array of items with extra spaces given text string, item start position, number of characters to return TRIM() --> removes extra spaces Thanks to Extended Office for the original formula to find the total number of items in a delimited list www.extendoffice.com/documents/excel/3134-excel-count-comma-separated-values-in-a-single-cell.html =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), "," ,""))+1 This formula only works for single character delimiters. I modified it slightly by dividing the above formula by the length of the delimiter. Also thanks to Extended Office for the original formula to extract text between commas, other delimiters www.extendoffice.com/documents/excel/4862-excel-extract-text-between-second-and-third-comma.html =TRIM(MID(SUBSTITUTE(A1, ",", REPT(" ", 100)), 200, 100)) This formula returns text between the second and third commas of a list. I modified it slightly by finding the total number of items in the list. My formula creates a column array. To make a row array wrap the formula in the TRANSPOSE() formula or switch the rows and columns arguments of the SEQUENCE() formula. Can also be used in conjunction with the TEXTJOIN() formula to combine multiple arrays into one array.
Deepak Chopra I can try to explain it in better detail. I have a spread sheet that I can upload somehow. ExcelIsFun please do make a video if you want of my List to Array formula
This could also be done using the single item method by finding the position of each item in the list and returning that item. Then copying the formula down would increment through the nth item
Is it possible to give more than 1 thumb up? The new dynamic formulas are really wonderful... what a pity they are not yet available to the general public...
Not available to the general public, yet. But they will. And since, in the long run, Microsoft says that Office 365 will be the ONLY version that they sell, eventually everyone will have them. It will be a huge shirt, because I have shown in earlier videos, even simple budget problems offer a choice of the old way where you create formulas and copy to other cells, or the new dynamic array way, like in this video: ua-cam.com/video/patDv_7z0Q4/v-deo.html
From the preview I was thinking this was going to be a freakish formula. Excel is making it easier, but I would never have been able to figure out how to join all those items in a cell without your teaching. Thank you Mike.
That ALT+ENTER bonus formula trick is awesome. Haven’t used it like that before. I will now. These Excel Quickies really are fun to watch: they are like a quick snack on the go, always with a cherry on top... Love it! (I like your video accelerator tricks, too, BTW)
Hi Mike.. very cool use of new Dynamic Array functions and TEXTJOIN. Inspired by your solution and just for the nerdy EXCEL fun of it, I took a stab at solving it with traditional CSE array formulas. This is what I came up with: Cell I5 (copy down): ={INDEX(B$5:B$16,MATCH(0,COUNTIF(I$4:I4,B$5:B$16),0))} Cell J5 (copy down): ={INDEX(C$5:C$16,MATCH(0,COUNTIF(J$4:J4,C$5:C$16),0))} Cell K5 (copy down): ={SUBSTITUTE(SUBSTITUTE(TEXTJOIN(CHAR(10),FALSE,IF($B$5:$B$16&$C$5:$C$16=I5&J5,$G$5:$G$16)),CHAR(10)&"FALSE",""),"FALSE"&CHAR(10),"")} Remember to enable Wrap Text and size row height, as needed for K5:K8.. as you did in the video. I got the same results.. so.. there you go. Credit to you, your great channel, videos, PDF notes, etc. as well as your CTRL+SHIFT+ENTER book. Six months ago, I would not have had a clue where to begin. With your great instructional resources.. I was able to put it together : )) Thumbs up ExcelIsFun!!
@@excelisfun Thanks Mike.. all due to your great channel and resources, books, etc. You truly make learning Excel fun.. and rewarding.. as I can take new skills and immediately put them to use in my work. Go Team ExcelIsFun!!
Thanks Mike. Over the Top!!!! Bonus formula.....Alt+Enter....I use it all the time, thanks to you from previous videos. Well, all i know is from previous videos :) :) .....Char(10) i stopped using it after you showed the Alt+Enter. Great Great Great!!!!
Another commenter said he got them and does not have the Insider Edition. That would be big news. I have not heard about this release yet. I asked him what build he has...
Glad you loved it, RRR! Thank you for your consistent support of the excelisfun channel, even when you can not watch the video on the day that it comes out : ) : ) : ) : )
Hi, Can you help me? I need to send out open orders and forecasts to suppliers on a monthly bases. Is there an easier way for me to have the suppliers separated from each other and pulled into a new worksheet per supplier?
@@roderickmose4691 I am surprised too. But strange things happen sometimes. That is why I e-mailed the head of the Dynamic Array Project and asked... We will see what he says.
Yes, it is almost a year... : ( Microsoft has not said what the delay is. It must be big, since they know that when it is released, so many people will have to switch to Office 365.
@@excelisfun I think the delay has more to do with the calculation engine than it does dynamic arrays. They said last fall that they did a partial rewrite of the calculation engine. As far as I know charts don't appear to update if they're based on dynamic arrays. Maybe the charting engine needs to be updated as well.
@@brianxyz I think you are right. I also think there was some issues with the engine interpreting array formulas and how it mistakenly used the SINGLE functions in many single cell array formula solutions.
Possible with helper column H5 =IF((B5=B6)*(C5=C6),COUNTIFS(B$5:B5,B5,C$5:C5,C5)&" :"&D5&"|"&E5&"|"&F5&CHAR(10)&H6,COUNTIFS(B$5:B5,B5,C$5:C5,C5)&" : "&D5&"|"&E5&"|"&F5) copy down K5 =INDEX($H$5:$H$16,MATCH(1,INDEX(($B$5:$B$16=I5)*($C$5:$C$16=J5),),)) copy down
Ya, I guess. I usually do. But even if we don't call it Boolean, multiplying does an AND logical test. Many of the textbooks I use in my statistics class do not call it Boolean. But I guess we Excel People do like that word Boolean ; )
Correct but to react specifically to your phrase "not an actual multiplication": The "*" operator both converts the Boolean T/F (text) to 1/0 (numbers) AND "actually multiplies" the resulting ones and zeros to performing the AND Test. Mike shows this clearly in countless other excellent videos. (This excel algorithm also permits SUMPRODUCT to do calculations with conditions (Boolean) similar to SUMIFS.)
Thanks that was gr8, Dear I have a problem in my excel sheet to solve, multiple persons each person have multiple ticket no. ticket price and ticket date in JAN, FEB, MAR, to DEC columns, Can you please help me in this, Please share your email so I can send you my excel sheet.
Be sure to check out the Bonus Formula at 05:08 : )
@Edy F I have NO idea how to do that. Try this great Excel Question site with 100s of Excel masters: mrexcel.com/forum
@Edy F Can you explain again with example for me ?
I hope i can help...
Brilliant Mike!
Glad it is brilliant for you, Roberto!!!! Thank you for your support!
Wow! I don't know i ever missed this. The countifs at the beginning was the first eye opener for me.
: )
Great Excel trick once again.
For anyone looking to create a single column or row array use the following dynamic array formula (can be entered with CSE or modified to do so):
=TRIM(MID(SUBSTITUTE(A1, Delimiter, REPT(" ", LEN(A1))), (SEQUENCE(1, (LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1, 1, 1) - 1)*LEN(A1) + 1, LEN(A1)))
A1 is the cell with the delimited list and Delimiter is the separator used (in Name Manager so it can be easily changed).
SUBSTITUTE(A1, Delimiter, REPT(" ", LEN(A1))) --> delimited list (with delimiter replaced with spaces) (used for MID() formula text argument)
(LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1 --> finds total number of items in list
SEQUENCE(1, (LEN(A1) - LEN(SUBSTITUTE(A1, Delimiter, ""))) / (LEN(Delimiter)) + 1, 1, 1) --> finds item position in string (used for MID() formula start_num argument)
LEN(A1) --> finds total number of characters in list string (used for MID() formula num_chars argument)
MID() --> creates array of items with extra spaces given text string, item start position, number of characters to return
TRIM() --> removes extra spaces
Thanks to Extended Office for the original formula to find the total number of items in a delimited list
www.extendoffice.com/documents/excel/3134-excel-count-comma-separated-values-in-a-single-cell.html
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), "," ,""))+1
This formula only works for single character delimiters. I modified it slightly by dividing the above formula by the length of the delimiter.
Also thanks to Extended Office for the original formula to extract text between commas, other delimiters
www.extendoffice.com/documents/excel/4862-excel-extract-text-between-second-and-third-comma.html
=TRIM(MID(SUBSTITUTE(A1, ",", REPT(" ", 100)), 200, 100))
This formula returns text between the second and third commas of a list. I modified it slightly by finding the total number of items in the list.
My formula creates a column array. To make a row array wrap the formula in the TRANSPOSE() formula or switch the rows and columns arguments of the SEQUENCE() formula. Can also be used in conjunction with the TEXTJOIN() formula to combine multiple arrays into one array.
Nice... but little too complicated for me to understand - ExcelsFun can you please make a video on this
Deepak Chopra I can try to explain it in better detail. I have a spread sheet that I can upload somehow. ExcelIsFun please do make a video if you want of my List to Array formula
I’ll upload it to my google drive or OneDrive and share the website address. I broke the formula down step by step in the spreadsheet
This could also be done using the single item method by finding the position of each item in the list and returning that item. Then copying the formula down would increment through the nth item
@@patrickschardt7724 Thanks
Bonus formulas are back by very popular demand!! In this case the bonus formula is the way to go!
I still like CHAR(10)... It is so explicit... Thanks for your support, Richard Hay!!!
Thanks for bonus. It's useful to know that ALT+Enter can be used in formula
Alt + Enter in formula or in cell : )
Thank you for another magical video Mike, checking the last cell is also something very important that you emphasise. 🤗
Yes, we all avoid errors by following this helpful rule: F2!!!!
Is it possible to give more than 1 thumb up? The new dynamic formulas are really wonderful... what a pity they are not yet available to the general public...
Not available to the general public, yet. But they will. And since, in the long run, Microsoft says that Office 365 will be the ONLY version that they sell, eventually everyone will have them. It will be a huge shirt, because I have shown in earlier videos, even simple budget problems offer a choice of the old way where you create formulas and copy to other cells, or the new dynamic array way, like in this video: ua-cam.com/video/patDv_7z0Q4/v-deo.html
From the preview I was thinking this was going to be a freakish formula. Excel is making it easier, but I would never have been able to figure out how to join all those items in a cell without your teaching. Thank you Mike.
You are welcome, N Sanch01!!!
First to comment
Awesome video bro
Very informative
I give you the First Place Trophy!!!!!!!
Great sir👌👌🙏🙏🙏🙏
Glad this helps!!!!!
That ALT+ENTER bonus formula trick is awesome. Haven’t used it like that before. I will now.
These Excel Quickies really are fun to watch: they are like a quick snack on the go, always with a cherry on top...
Love it!
(I like your video accelerator tricks, too, BTW)
I am not sure what you mean by "video accelerator tricks". What is that?
Mike, the small ‘jump cuts’ that speed up the flow of your videos.
@@GeertDelmulle , yes, most humans don't like long videos...
Mike you happen to have that great way and style of teaching. Thanks for all your training material shared
You are welcome, Awesh!!! Thanks for your kind words and thank you for your support with your comment, thumbs up and your Sub : )
Hi Mike.. very cool use of new Dynamic Array functions and TEXTJOIN. Inspired by your solution and just for the nerdy EXCEL fun of it, I took a stab at solving it with traditional CSE array formulas. This is what I came up with:
Cell I5 (copy down): ={INDEX(B$5:B$16,MATCH(0,COUNTIF(I$4:I4,B$5:B$16),0))}
Cell J5 (copy down): ={INDEX(C$5:C$16,MATCH(0,COUNTIF(J$4:J4,C$5:C$16),0))}
Cell K5 (copy down): ={SUBSTITUTE(SUBSTITUTE(TEXTJOIN(CHAR(10),FALSE,IF($B$5:$B$16&$C$5:$C$16=I5&J5,$G$5:$G$16)),CHAR(10)&"FALSE",""),"FALSE"&CHAR(10),"")}
Remember to enable Wrap Text and size row height, as needed for K5:K8.. as you did in the video.
I got the same results.. so.. there you go. Credit to you, your great channel, videos, PDF notes, etc. as well as your CTRL+SHIFT+ENTER book. Six months ago, I would not have had a clue where to begin. With your great instructional resources.. I was able to put it together : )) Thumbs up ExcelIsFun!!
Wow!!!!! That is amazing how far you have come in 6 months!!!!! The slope on that line is HUGE, near infinity : )
@@excelisfun Thanks Mike.. all due to your great channel and resources, books, etc. You truly make learning Excel fun.. and rewarding.. as I can take new skills and immediately put them to use in my work. Go Team ExcelIsFun!!
@@wayneedmondson1065 Go Team : ) : )
Thank you
You are welcome, MrsCyImsofly
!!! Thank you for your support with your comment, thumbs up and Sub : )
Thanks Mike. Over the Top!!!! Bonus formula.....Alt+Enter....I use it all the time, thanks to you from previous videos. Well, all i know is from previous videos :) :) .....Char(10) i stopped using it after you showed the Alt+Enter. Great Great Great!!!!
That is so funny, I still prefer CAHR(10)...
Great text joining tricks, thanks Mike
Glad you you like the joining text tricks, Ogwal!
Thank you for the video :-)
Epic Mike. I don’t yet have the new dynamic array formulas in my Office 365. Can’t wait to get them. Thanks.
Another commenter said he got them and does not have the Insider Edition. That would be big news. I have not heard about this release yet. I asked him what build he has...
Office 365 MAGIC Dynamic Arrays :). Thanks for video :)
You are welcome for the Dynamic Magic!!!!
This one is awesome mike.... Loved it
Glad you loved it, RRR! Thank you for your consistent support of the excelisfun channel, even when you can not watch the video on the day that it comes out : ) : ) : ) : )
Very helpful. Thank You Mike
You are welcome for the helpful trick, nimrodzik1!!!!
Hi Mike, really helpful
Glad it helps. Tomorrows video should be good too! Thanks for your support on each video you watch, VIPUL!!!!
Wow...Thank you. This is AWESOME. Unfortunately I am not an insider and can’t wait until it is available in the pro version.
Yes, the Dynamic Arrays and the new Calc Engine make most all of our solutions sorter and easier to understand : ) Can't wait for you to get it!
Fantastic thank you for sharing!
You are welcome for the share, Roman! Thanks for your support : )
Hi, Can you help me? I need to send out open orders and forecasts to suppliers on a monthly bases. Is there an easier way for me to have the suppliers separated from each other and pulled into a new worksheet per supplier?
How do we put as list in dropdown list, and returning value one word [column] only of texts joined onto cell?
I highly requested you. Please make tutorial video for material inventory software and hotel booking software
Amezing .. Thank you
You are welcome, Bindhyesh! Thanks for your support with your comment, thumbs up and Sub : )
Thanks Mike
You are welcome, Dave, and as always i appreciate your consistent support : )
Cool as ever ... Can we reverse this process some how? I have data as in Column L and I want it in different rows as in Column G
So you have a delimited list that you want to make an array of? See my other comment
oh very Nice.I don't have office 365 yet so I will go with count if.
Cool, Phone Excel!!!
Now Im gonna fire on team group
Thanks a lot
You are welcome, a lot!!!!
I have O365 but not the Insider Edition and I have the dynamic array formulas going about a month now.
Really!?!?!? Are you sre? When you go to the File menu and then to Account, does it really NOT say Insider? Do you know what build number you have?
@@excelisfun Hi Mike, yes I'm certain. I'm on O365 ProPlus version 1907 (Build 11901.20176)
@@roderickmose4691 Thanks, roderick!!!! I sent an e-mail to Microsoft asking for clarification. Hopefully Joe will answer soon. I will post back soon.
@@excelisfun I'm surprised no one else had commented here that they have the non-Insider edition with DAF as well. Certainly, there must be others.
@@roderickmose4691 I am surprised too. But strange things happen sometimes. That is why I e-mailed the head of the Dynamic Array Project and asked... We will see what he says.
Same work how to do in office 2013, because text join formula not available
When it will be generally available. It's almost one year now :((((
Yes, it is almost a year... : ( Microsoft has not said what the delay is. It must be big, since they know that when it is released, so many people will have to switch to Office 365.
@@excelisfun I think the delay has more to do with the calculation engine than it does dynamic arrays. They said last fall that they did a partial rewrite of the calculation engine. As far as I know charts don't appear to update if they're based on dynamic arrays. Maybe the charting engine needs to be updated as well.
@@brianxyz I think you are right. I also think there was some issues with the engine interpreting array formulas and how it mistakenly used the SINGLE functions in many single cell array formula solutions.
What about Off 2013? is it possible?
Yes, it is, but it is a huge difficult array formula. I have not worked it out yet, though... : (
Possible with helper column
H5
=IF((B5=B6)*(C5=C6),COUNTIFS(B$5:B5,B5,C$5:C5,C5)&" :"&D5&"|"&E5&"|"&F5&CHAR(10)&H6,COUNTIFS(B$5:B5,B5,C$5:C5,C5)&" : "&D5&"|"&E5&"|"&F5)
copy down
K5
=INDEX($H$5:$H$16,MATCH(1,INDEX(($B$5:$B$16=I5)*($C$5:$C$16=J5),),))
copy down
Might you have wanted to note that 'multiply' is doing a Boolean AND not an actual multiply?
Ya, I guess. I usually do. But even if we don't call it Boolean, multiplying does an AND logical test. Many of the textbooks I use in my statistics class do not call it Boolean. But I guess we Excel People do like that word Boolean ; )
Correct but to react specifically to your phrase "not an actual multiplication": The "*" operator both converts the Boolean T/F (text) to 1/0 (numbers) AND "actually multiplies" the resulting ones and zeros to performing the AND Test. Mike shows this clearly in countless other excellent videos. (This excel algorithm also permits SUMPRODUCT to do calculations with conditions (Boolean) similar to SUMIFS.)
There is not unique, sequence, filter, textjoe formula in my excel 2007.
No there is not. It is ONLY in Office 365 Insider.
Thanks that was gr8, Dear I have a problem in my excel sheet to solve, multiple persons each person have multiple ticket no. ticket price and ticket date in JAN, FEB, MAR, to DEC columns, Can you please help me in this, Please share your email so I can send you my excel sheet.