Concatenate IF
Greetings to all,
There will be times where you need to concatenate / combine text from a range of cells into a single cell, perhaps for a dashboard or reporting purposes. Issue is, CONCATENATE or using ampersand "&" requires reference to single cells which can get tedious when numbers go above 10.
Here, we explore a workaround that combines text from a range of cells into a single cell based on a criteria (Salespersons with Profits above $25,000).
Workaround Step 1 (Prepare a helper column with blank cell above 1st entry)
Workaround Step 2 (In helper column's 1st entry, refer to blank cell above & "," as separator / delimiter & Salesperson's name on the same row)
E.g. =E4&","&A5 (Keep references relative for copying down), enter to confirm entry
Workaround Step 3 (Double click bottom right corner of 1st entry in helper column to Concatenate all)
Workaround Step 4 (Nest original 1st entry formula within TRUE syntax of "IF" function, refer to cell above for FALSE syntax)
E.g. =IF(D5>25000,E4&","&A5,E4)
Workaround Step 5 (Double click bottom right corner of 1st entry in helper column to Concatenate Salespersons with Profits >25000)
Workaround Step 6 (For dashboard, refer to last entry in helper column less 1st character on its left)
E.g. =RIGHT(OFFSET($E$4,COUNTA($D$5:$D$15),),LEN(OFFSET($E$4,COUNTA($D$5:$D$15),))-1)
COUNTA($D$5:$D$15) - Count the number of non blank cells within conditional column (Profits), last reference for maximum number of entries possible
OFFSET($E$4,COUNTA($D$5:$D$15),) - Identify last entry in helper column, starting reference set as blank cell above 1st entry in helper column
LEN(OFFSET($E$4,COUNTA($D$5:$D$15),))-1 - Count the number of characters in last entry in helper column less 1 (Removes starting ",")
Share with us if the above has helped and your preferred way of concatenating text from a range of cells.
