Array Formula - List All Suppliers by Material in Single Cell with Line Feed, Excel Magic Trick 1579

In this video we create a Material Supplier Report with All Suppliers Listed in Single Cell With Line Feed. First we joining three columns with Supplier Data, Then create a Running Count for each Material, then use the Office 365 Dynamic Array Functions UNIQUE & FILTER to create the report. Also see the Functions: COUNTIFS, TEXTJOIN, and CHAR.

Related Videos:
Power Query – List All Suppliers by Material in Single Cell with Line Feed, Excel Magic Trick 1580
Count, Join then Lookup Suppliers & List in Single Cell With Line Feed, Excel Magic Trick 1579
Unique Invoice / Vendor List with Items Listed Horizontally - Excel Dynamic Array Formula- EMT 1575
Power Query M Code Group By Formula to Transform Invoice Table - Excel Magic Trick 1576

[excelisfun]

Be sure to check out the Bonus Formula at 05:08 : )

[patrickschardt]

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
=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
=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.

[vida]

Thanks for bonus. It's useful to know that ALT+Enter can be used in formula

[nsanch]

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.

[katerina]

Thank you for another magical video Mike, checking the last cell is also something very important that you emphasise. 🤗

[bevonclarke]

Wow! I don't know i ever missed this. The countifs at the beginning was the first eye opener for me.

[richardhay]

Bonus formulas are back by very popular demand!! In this case the bonus formula is the way to go!

[wayneedmondson]

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!!

[FabioGambaro]

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...

[johnborg]

Thanks Mike. Over the Top!!!! Bonus use it all the time, thanks to you from previous videos. Well, all i know is from previous videos :) :) i stopped using it after you showed the Alt+Enter. Great Great Great!!!!

[GeertDelmulle]

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)

[AweshBhornya-ExcelforNewbies]

Mike you happen to have that great way and style of teaching. Thanks for all your training material shared

[pranavshah]

First to comment
Awesome video bro
Very informative

[chrism]

Epic Mike. I don’t yet have the new dynamic array formulas in my Office 365. Can’t wait to get them. Thanks.

[JanBolhuis]

Wow...Thank you. This is AWESOME. Unfortunately I am not an insider and can’t wait until it is available in the pro version.

[ogwalfrancis]

Great text joining tricks, thanks Mike

[MalinaC]

Office 365 MAGIC Dynamic Arrays :). Thanks for video :)

[AbdurRahim-otgp]

I highly requested you. Please make tutorial video for material inventory software and hotel booking software

[simfinso]

oh very Nice.I don't have office 365 yet so I will go with count if.

[albhenpa]

How do we put as list in dropdown list, and returning value one word [column] only of texts joined onto cell?