Hello,
Apologies if my description is not clear (it is super clear in my head!)
I am trying to build a formula that looks at a series of cells and returns the description of the non blank cells. I can write a formula if only had to check 1 cell --> IF(ISBLANK(D3), " ", D2) but I have multiple cells and in some cases there are values in more than one cell.
Attached the excel that will probably be clearer than my explanation.
Thanks all
You can use this formula in cell J3:
=SUBSTITUTE(IF(B3="","",B$2&"/")&IF(C3="","",C$2&"/")&IF(D3="","",D$2&"/")&IF(E3="","",E$2&"/")&IF(F3="","",F$2&"/")&IF(G3="","",G$2&"/")&IF(H3="","",H$2&"/")&IF(I3="","",I$2&"/")&"/","//","")
then copy down.
Note that you may have to change the commas ( , ) in the formula to semicolons ( ; ), depending on your regional settings.
Hope this helps.
Pete
Hi
not sure you can exploit TEXTJOIN on your Excel version...a possible approach could be:
=IFERROR(INDEX($B$2:$I$2,AGGREGATE(15,6,COLUMN(B:I)-1/(B3:I3<>0),1)),"")&IFERROR("/"&INDEX($B$2:$I$2,AGGREGATE(15,6,COLUMN(B:I)-1/(B3:I3<>0),2)),"")&IFERROR("/"&INDEX($B$2:$I$2,AGGREGATE(15,6,COLUMN(B:I)-1/(B3:I3<>0),3)),"")
to get the fourth istance and so on
&IFERROR("/"&INDEX($B$2:$I$2,AGGREGATE(15,6,COLUMN(B:I)-1/(B3:I3<>0),4)),"")
Regards
Last edited by canapone; 04-17-2019 at 05:09 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
SUPER!! Works great. Thanks so much!
TEXTJOIN is available only in the Office 365 subscription model and the 2019 Desktop version.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As I see you've got Excel2013 so you can't use TEXTJOIN.
Put this into VBA module:
and then, You put into J3:
Please Login or Register to view this content.
accept with Ctrl+Shift+Enter (array function, you should get { } around formula).Please Login or Register to view this content.
Then drag it down as needed.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks