+ Reply to Thread
Results 1 to 16 of 16

Insert text if not present in specific columns

  1. #1
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Insert text if not present in specific columns

    In columns E through L I have a series of names, each one different than the last. In column M I want to insert a name based on a predetermined list but only if that name is not present in the cells E through L and if the cell in column M is blank. If M2 is populated then it should move to M3, and so on. I purposely left column M blank in my example to illustrate that the column will be blank when starting out.

    E F G H I J K L M
    Bob Dan Denise Lenora Michael Lydia Nikk John
    Nikk Denise Dan Lydia Mercedes Michael Ted
    Bob Dan Denise Lenora Michael Lydia Nikki Bill
    Nikk Denise Dan Lydia Mercedes Michael John
    Bob Dan Denise Lenora Michael Lydia Nikki Ted
    Nikk Denise Dan Lydia Mercedes Michael Bill
    Nikk Denise Dan Lydia Mercedes Michael John
    Nikk Denise Dan Lydia Mercedes Michael Ted

    Any thoughts on where I could being?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,239

    Re: Insert text if not present in specific columns

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    Let's see if this works. I have attached the sample file based on the reply. Apologies for not including it initially.

    If anything needs to be explained further please let me know. I did attempt to indicate what I am looking for on Column N of the 'Ideal Outcome' tab.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    399

    Re: Insert text if not present in specific columns

    Try this array formula
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,246

    Re: Insert text if not present in specific columns

    You could try this if you're looking for a macro solution.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    I tried using your array but I get an error. It looks like a comma is out of place or the formula is not written correctly. I also copied and pasted as you provided but no dice.

  7. #7
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    Quote Originally Posted by jolivanes View Post
    You could try this if you're looking for a macro solution.
    Please Login or Register  to view this content.
    Looks like this works fairly well.

    Do you know if there is a way to have the macro only allow three (3) of the same name? For example, Bob should only appear 3 times, same as other names.

  8. #8
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    399

    Re: Insert text if not present in specific columns

    Quote Originally Posted by klturi421 View Post
    I tried using your array but I get an error. It looks like a comma is out of place or the formula is not written correctly. I also copied and pasted as you provided but no dice.
    See attachment.
    Besides, each name only appear 3 times in columns A through H or only column H?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,246

    Re: Insert text if not present in specific columns

    Please don't quote whole posts. Just a bunch of not needed clutter.
    Refer to post numbers if required.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    I apologize for quoting entire posts, wont do that again!

    I attempted running the macro on a larger set of data but the full list of names did not run and some of the ones that were entered were only entered once. When I ran it on the smaller and original set of data I provided it worked perfectly.

    I have re-uploaded a second iteration with the macro included and linked to a button to show the results when it runs.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,636

    Re: Insert text if not present in specific columns

    Try
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,246

    Re: Insert text if not present in specific columns

    How do you determine what is right or wrong if as many as 7 out of 8 names are missing in a Row. Which missing name is the "right" one?

  13. #13
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    The only way that I think I can properly explain that piece is to split some of the "rules" up.

    1. If a name is not present in the first 7 cells then it should enter the first available name based on the list of available names. Example, if Bob, Dan, and Denise are not present in the first 7 cells then "Bob" would be selected first as he is first, alphabetically speaking.

    2. If a name is not present in the first 7 cells and has not yet exceeded the 3 name limit then the next available name should be entered. Example. Bob and Dan both have had their names entered 3 times where as Denise's name has only been entered twice, she should be entered next.

  14. #14
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    Jindon, I initially thought your code was working but it appears that it's just selecting the first name in the first column with a name and repeating it in column H. It is only adding the name in 3 times but it's not selecting a new name from the list. For example, A2 is blank and B2 has Dan, H2 which was originally blank then has Dan after running the macro.
    Last edited by klturi421; 05-30-2019 at 10:28 PM.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,636

    Re: Insert text if not present in specific columns

    Oops, of course..
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: Insert text if not present in specific columns

    That works so far! I will test it out a few times and report back but so far so good.

    Thanks

+ 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. Formula to see if text in cell A1 is present in specific filepath
    By jbbaxter04 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2017, 10:21 AM
  2. [SOLVED] Search many workbooks for specific text, then insert a text in adjacent cell
    By MDW12 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-26-2017, 02:08 PM
  3. [SOLVED] If a cell contains text insert specific text in specific cells in row
    By muss1210 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-18-2017, 10:51 AM
  4. [SOLVED] Look for specific text string, insert row below, insert text to that row
    By jwats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2015, 03:08 PM
  5. combining text with comma if any of the cells data is present in 3 columns
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2013, 10:02 AM
  6. [SOLVED] Create formula to sum column total if specific text is present in second column
    By Banir in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 07:48 AM
  7. [SOLVED] Merge/unmerge based on whether specific text is present in a column
    By susanbarbour in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2012, 09:08 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1