Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN

Показать описание

Download Excel Files:

Learn how to Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN (New Excel 2016 Function).

Рекомендации по теме

Комментарии

TEXTJOIN is a very useful function. Thanks for posting these videos.

timwhite

Oh my god, what did I just go through! You are superb. Thanks.

sim

Hi guys, I've been watching you videos for months and I love them all, I've learn a lot.
Now I'm trying to find a formula to track the amount of bits of the worksheet I'm creating but with little success. Do you have any suggestions?

oxistu

sir can you teach me multiple if excel formula and If and formula I did not understand please explain to me with an example

soniarunsoni

So is it the delimiter argument of the TEXTJOIN function that is programmed to know when it moves down a row to add the word "or" or is it the array constant in the delimiter argument that knows to move down a row and add the word "or"? Hope that makes sense.

Sal_A

+ExcellsFun
I am in hopes of some help. I've created a form for people to type information into that automatically fills into Google Sheets. The data needs to be combined from columns I, J, K, L into column H automatically when data is automatically dumped into Google Sheets.
MODIFIED TEXT BELOW:
I found a way to do this with the following:
=ARRAYFORMULA(IF(LEN(B2:B), I2:I&J2:J&K2:K&L2:L, IFERROR(1/0)))
Also tried the following with the same result:
=ARRAYFORMULA(IF(B3:B<>"", (I3:I&J3:J&K3:K&L3:L), ))
This data is then dumped automatically into "Awesome Tables" via link to particular sheet.
However, when the table displays it shows 1000 empty rows below the last actual entered row. Is there anyway to stop this from happening? I have the following ArrayFormula to create a unique number but this does *NOT* cause the rows to expand to 1000 empty rows below the last row.
=ArrayFormula(if(B3:B<>"", row(3:9003)+1000, ))

BrowserTheWhippet

Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN

Excel Magic Trick 1308: Concatenate Items in Rows & Columns from Two-Way Table with TEXTJOIN

Excel Magic Trick 1302: List Text Modes in Single Cell with TEXTJOIN, HLOOKUP, MATCH & MODE.MULT

Excel Magic Trick 1301: Sorted List of Modes in Single Cell with TEXTJOIN Array Formula

Excel Magic Trick 1309: Extract Unique List and List in Single Cell: TEXTJOIN Array Formula

Microsoft Excel - Concatenate multiple cells tutorial

Excel Magic Trick 1305: Adding Multiple Two-Way Lookups INDEX function & N(IF(1,Array) Trick

Excel is Magic How to use CONCATENATE formula in Excel #Trick 6

Excel Magic Trick 1295: TEXTJOIN & INDEX Function to create Random Description Codes

Excel Magic Trick 1300: List All Modes in Single Cell with TEXTJOIN & MODE.MULT

Excel Magic Trick 1306: Simulate VLOOKUP Helper Column In Array Formula using T(IF({1},Array) Trick

Excel Magic Trick 1290: VLOOKUP to Multiple Dynamic Lookup Tables Listed as Single Downloaded Table

Excel Formulas and Functions Concatenate

Excel Magic Trick 1307: Conditionally Format Multiple Non-Adjacent Cells in Cross Tabulated Table

Excel Concatenate and Text to column

Difference in TEXTJOIN against CONCATENATE against & ' '& ?

Excel Magic Trick 1317: Formulas for Total Insulin Units… And Excel’s Golden Rule & MAX Replaces...

How to Combine (Concatenate) Data from Multiple Rows into One Cell in Excel

Find Exact Number of ROW and COLUMNS and Configure CONCATENATE in MS Excel

Excel Magic Trick 1310: Minimum Value Greater Than Zero with: 1) SMALL & COUNTIFS, 2) LARGE?

Excel Magic Trick 765: Formula To Sort Top 3, Including Ties

Excel Magic Trick 1135: Rank Values From Two Columns: RANK.AVE, RANK.EQ & RANK Functions

Excel Magic Trick 1314: Array Formula To Create Sorted Unique List with Mixed Data

Excel Formula Evolution: Formulas Can Spill Now - Episode 2233

Excel Tutorial For Beginners - How To Use Concatenate Function | MS Tools Tutorials