+ Reply to Thread
Results 1 to 14 of 14

Repeating IF?

  1. 10-18-2016, 09:29 AM #1
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Repeating IF?

    Hi,

    I wonder if anyone can help me, on the attached spreadsheet there is a list of codes for different divisions which I need to group together in one cell separated by |. Obviously this can be easily done but I need for anyone divisions with "not quoted" to be skipped out without leaving a space or anything. An example of how I want it to look is on the right under the title "Answer needed for the system in 1 column". I've had a go (in green) but don't know how to get the IF to repeat itself. Does anybody know how to do it either this way or another way? I will eventually need to apply it to a large amount of data. Please let me know if this doesn't make sense.

    Thanks in advance for any help you can give me.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-18-2016, 09:35 AM #2
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Repeating IF?

    It's okay, I think I've solved this one. Thanks for looking!
    Reply With QuoteRegister To Reply
  3. 10-18-2016, 09:41 AM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Repeating IF?

    Maybe

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 10-18-2016, 09:42 AM #4
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Repeating IF?

    Thanks very much for replying, that's exactly the same as what I got!
    Reply With QuoteRegister To Reply
  5. 10-18-2016, 09:45 AM #5
    Kasper(Spreadsheeto)
    Kasper(Spreadsheeto) is offline
    Registered User
    Join Date
    08-22-2016
    Location
    Copenhagen, Denmark.
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: Repeating IF?

    I just played around with your sheet a bit. I think this is a better solution:

    Use the TEXTJOIN function (if you have excel 2016 - and 2013?) and a combination of IF and HLOOKUP. Put this in C2 and drag down through C8.

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


    Then put this in C9:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Kasper(Spreadsheeto); 10-18-2016 at 10:45 AM.
    Reply With QuoteRegister To Reply
  6. 10-18-2016, 09:50 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,418

    Re: Repeating IF?

    @Kasper - please attach the workbook directly here. Some members can't or don't wish to access file-sharing sites.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Also, please provide your solution in the body of your post as well as in the file. 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.
    Reply With QuoteRegister To Reply
  7. 10-18-2016, 10:34 AM #7
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Repeating IF?

    Hi,

    Does anybody know how I can stop there being a | at the end? So rather than having 2909072|2909074|2909075| it is just 2909072|2909074|2909075.

    Thanks
    Reply With QuoteRegister To Reply
  8. 10-18-2016, 10:39 AM #8
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Repeating IF?

    Enter this in I14
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

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


    to populate codes in from col A to col G
    Enter in A14 and copy across
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by AlKey; 10-18-2016 at 10:46 AM.
    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
    Reply With QuoteRegister To Reply
  9. 10-18-2016, 10:41 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,418

    Re: Repeating IF?

    Whose solution are you using?
    Reply With QuoteRegister To Reply
  10. 10-18-2016, 10:52 AM #10
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Repeating IF?

    Quote Originally Posted by sherlock1241 View Post
    Hi,

    Does anybody know how I can stop there being a | at the end? So rather than having 2909072|2909074|2909075| it is just 2909072|2909074|2909075.

    Thanks
    There's no trailing | symbol in the solution I gave in post #3

    @Kaper,
    I was assuming that the OP had records starting in row 14 extending down and that only columns A-G contained values and that the B2:B8 list was just a definitive list of all the codes in play. Maybe the OP will clarify.
    Reply With QuoteRegister To Reply
  11. 10-18-2016, 11:12 AM #11
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Repeating IF?

    Hi Richard,

    The issue with that is that if something is not quoted in the first column, it leaves a | at the beginning. Can you think of a way I can ensure that there are not |'s at the beginning or end?
    Reply With QuoteRegister To Reply
  12. 10-18-2016, 11:17 AM #12
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Repeating IF?

    Quote Originally Posted by sherlock1241 View Post
    Hi Richard,

    The issue with that is that if something is not quoted in the first column, it leaves a | at the beginning. Can you think of a way I can ensure that there are not |'s at the beginning or end?
    See post #8 the second formula
    Reply With QuoteRegister To Reply
  13. 10-18-2016, 11:36 AM #13
    sherlock1241
    sherlock1241 is offline
    Registered User
    Join Date
    02-03-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Repeating IF?

    Thanks so much for this AlKey, it worked. Saved me a lot of time.
    Reply With QuoteRegister To Reply
  14. 10-18-2016, 11:45 AM #14
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Repeating IF?

    You're welcome and thank you for the feedback
    Last edited by AlKey; 10-18-2016 at 11:56 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Repeating averages
    By pepsi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 09:03 AM
  2. [SOLVED] Without repeating numbers
    By thair in forum Excel General
    Replies: 6
    Last Post: 05-20-2012, 03:12 PM
  3. creating a repeating formula that doesn't show repeating values
    By cybershot in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-09-2010, 06:12 PM
  4. Repeating a seven row sum
    By TheLazyUpstart in forum Excel General
    Replies: 4
    Last Post: 08-11-2009, 06:17 AM
  5. Repeating sequences
    By TheExile in forum Excel General
    Replies: 3
    Last Post: 06-02-2008, 08:33 AM
  6. [SOLVED] non-repeating bg
    By stewart08 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-02-2008, 06:20 AM
  7. Repeating
    By Lynn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2005, 01:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1