+ Reply to Thread
Results 1 to 9 of 9

Inserting text to cells based on conditional formatting

  1. #1
    Registered User
    Join Date
    06-30-2021
    Location
    Dallas, Texas
    MS-Off Ver
    16
    Posts
    5

    Inserting text to cells based on conditional formatting

    I want to add a list of words in the part of the column under "IRU" (located in H216), as shown here:
    iru.png

    If cells B20-B131 contain "IRU" (with exact capitalization), I want to add the word from the adjacent cell to the list. For example, given that cell B124 (highlighted in green) contains "IRU": cell.png, I want to add the value in cell A124 (polyester): A.png to a cell under IRU as shown earlier. What formula can I use to accomplish this?

    Edit: also attached is the excel sheet.
    Attached Files Attached Files
    Last edited by mhanna112; 07-05-2021 at 02:20 AM.

  2. #2
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: Inserting text to cells based on conditional formatting

    Try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I suppose that you have IRU text in C1
    Diana Tanase

  3. #3
    Registered User
    Join Date
    06-30-2021
    Location
    Dallas, Texas
    MS-Off Ver
    16
    Posts
    5

    Re: Inserting text to cells based on conditional formatting

    This only works for the first match. Can I alter the formula to retrieve all matches, rather than just one?

  4. #4
    Registered User
    Join Date
    06-30-2021
    Location
    Dallas, Texas
    MS-Off Ver
    16
    Posts
    5

    Re: Inserting text to cells based on conditional formatting

    Quote Originally Posted by tanasedn View Post
    Try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I suppose that you have IRU text in C1
    This only works for the first match. Can I alter the formula to retrieve all matches, rather than just one?

  5. #5
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: Inserting text to cells based on conditional formatting

    Quote Originally Posted by mhanna112 View Post
    This only works for the first match. Can I alter the formula to retrieve all matches, rather than just one?
    In this particular case, without VBA, in column C in first cell type the word you want("IRU") and in C2 insert the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And then sort by this column. The cells with values will appear at top and they are associated with text in column B that contain "IRA"
    Otherwise, if you need the values to be extracted in other worksheet than, is better to use VBA!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Inserting text to cells based on conditional formatting

    I suggest that you upload a sample WB - see the yellow banner above to see how to
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    06-30-2021
    Location
    Dallas, Texas
    MS-Off Ver
    16
    Posts
    5

    Re: Inserting text to cells based on conditional formatting

    Quote Originally Posted by FDibbins View Post
    I suggest that you upload a sample WB - see the yellow banner above to see how to
    Thank you. I just updated!

  8. #8
    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: Inserting text to cells based on conditional formatting

    There are no manually added expected results in your sample file, so it remains as clear as mud (to me).
    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.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,325

    Re: Inserting text to cells based on conditional formatting

    In H217

    =IFERROR(INDEX($A$2:$A$131,AGGREGATE(15,6,(ROW($A$2:$A$131)-1)/(FIND("IRU",$B$2:$B$131)>0),ROWS($1:1))),"")

    Copy down
    Last edited by JohnTopley; 07-05-2021 at 05:44 AM.

+ 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. 2 conditioal outputin 1 column based on max values
    By pmbhome in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2021, 06:23 AM
  2. conditional formatting fill cells based on text, how to find sum based on CF colour
    By tubbybear in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-27-2016, 12:04 PM
  3. [SOLVED] Formatting a cell with a colour based on text in other cells.
    By Borneorose in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2013, 08:24 AM
  4. Replies: 1
    Last Post: 04-19-2013, 09:47 AM
  5. Conditional Formatting based on text in previous cells
    By avidcat in forum Excel General
    Replies: 3
    Last Post: 07-14-2012, 01:01 PM
  6. VBA code for inserting formula, based on another cells text value.
    By Pancho3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2011, 04:30 PM
  7. Conditioal formatting problem
    By HDV in forum Excel General
    Replies: 3
    Last Post: 09-22-2005, 10:05 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.06996 seconds
  • Memory Usage 9,140KB
  • 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
  • (3)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (9)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (9)postbit_legacy
  • (9)postbit_onlinestatus
  • (9)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