+ Reply to Thread
Results 1 to 6 of 6

Retun values from non blank cells and combine results from multiple cells

  1. #1
    Registered User
    Join Date
    04-16-2019
    Location
    France
    MS-Off Ver
    2013
    Posts
    2

    Retun values from non blank cells and combine results from multiple cells

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Retun values from non blank cells and combine results from multiple cells

    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

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Retun values from non blank cells and combine results from multiple cells

    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.

  4. #4
    Registered User
    Join Date
    04-16-2019
    Location
    France
    MS-Off Ver
    2013
    Posts
    2

    Re: Retun values from non blank cells and combine results from multiple cells

    SUPER!! Works great. Thanks so much!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Retun values from non blank cells and combine results from multiple cells

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,500

    Re: Retun values from non blank cells and combine results from multiple cells

    As I see you've got Excel2013 so you can't use TEXTJOIN.
    Put this into VBA module:

    Please Login or Register  to view this content.
    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).
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advanced Filter to show all Results, even Rows with Multiple Blank Cells
    By bellaexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2018, 01:25 PM
  2. Sub to remove duplicate IDs and combine multiple lookup results into single cells
    By Max, Singapore in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2017, 04:30 AM
  3. [SOLVED] Need to combine cells to one string of text if duplicate values exist in multiple cells
    By Chris McGlothen in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-17-2017, 09:13 PM
  4. [SOLVED] Combine text in multiple cells if not blank
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-09-2017, 10:26 PM
  5. [SOLVED] Combine multiple cells text into one cell, skip blank cells.
    By T15K in forum Excel General
    Replies: 6
    Last Post: 12-05-2014, 09:49 PM
  6. Combine text in multiple cells if adjacent column is blank
    By problematic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2012, 01:52 PM
  7. Replies: 3
    Last Post: 04-27-2011, 12:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07774 seconds
  • Memory Usage 9,057KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (6)postbit_legacy
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1