Hello,
I'd like to create a formula that checks a range to find nonblank cells and then returns values from cells that correspond to those nonblanks that were found. I want all the returned values to be aggregated in a single cell separated by commas.
See Column B in the attachment for an example of how I'd like the formula to work.
Thanks!
Erin
You can put this in B2:
=SUBSTITUTE(SUBSTITUTE(IF(C2="","",C$1&", ") & IF(D2="","",D$1&", ") & IF(E2="","",E$1&", ") & IF(F2="","",F$1&", ") & IF(G2="","",G$1&", ") & "xx",", xx",""),"xx","")
then copy down.
Hope this helps.
Pete
There is no a perfect way of concatenating text strings with a formula unless you are a subscriber of Office 365 which offers a great excel function TEXTJOIN.
However, there is a good news! You can get the same functionality with a VBA version.
Here is how to do this:
1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.
Don't forget to save your worbook as Macro-Enabled workbook.
http://www.get-digital-help.com/2016...tjoin-function
Then enter array formula in a cell B2 and copy formula down
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks