+ Reply to Thread
Results 1 to 13 of 13

What Excel function would you use to recreate an ever in creasing number of text Results

  1. #1
    Registered User
    Join Date
    04-18-2014
    Location
    Surrey
    MS-Off Ver
    Excel 2007
    Posts
    5

    What Excel function would you use to recreate an ever in creasing number of text Results

    hello, I am hoping that someone might be able to help a frustrated user, I am trying to create the values in the 2nd column based on values in first column
    In cell A1 , it looks at the previous figure in column A, and because its different( there isn't a figure prior to this), it thinks it is different so the first value in A1 will read Text_01. When looking at A2, it looks at value in A1 and because different returns a value of Text_01 again because they are different, it then looks at A3 and because this is different from A2 it returns a value Text_01 again. However when looking at A4 because it detects that it is the same as A3 it returns a value of Text_02. When looking at A4 because it differs from A3 it again returns a value of Text_01 , and so on

    Is there a function in Excel that can do this for me, or do I need to get an additional program in which case can anyone advise where I can get

    many thanks in advance for your help



    5052267066355 Text_01
    5052267066577 Text_01
    5052267066645 Text_01
    5052267066645 Text_02
    5052267066652 Text_01
    5052267066676 Text_01
    5052267066676 Text_02
    5052267066713 Text_01
    5052267067796 Text_01
    5052267067796 Text_02
    5052267067833 Text_01
    5052267067840 Text_01
    Last edited by mjd128; 04-18-2014 at 02:33 PM. Reason: asked to change title

  2. #2
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: help with an excel function

    Yes. In B1:

    Text_01

    In B2, copied down:

    If(A2=A1,"Text_02","Text_01")
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: help with an excel function

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    04-18-2014
    Location
    Surrey
    MS-Off Ver
    Excel 2007
    Posts
    5

    What Excel function would you use to recreate an ever in creasing number of text Results

    hello, many thanks for that, that was greatupo
    I should have said sometimes you can get as many as 10 and maybe more , how would I approach this is it needed to return greater numbers, ie up to Text_50

    would I need a program as the formula would be quite complicated, the column could read:

    5052267069936 Text_01
    5052267070062 Text_01
    5052267070161 Text_01
    5052267070185 Text_01
    5052267070284 Text_01
    5052267070314 Text_01
    5052267070321 Text_01
    5052267070376 Text_01
    5052267070376 Text_02
    5052267070376 Text_03
    5052267070376 Text_04
    5052267070376 Text_05
    5052267070376 Text_06
    5052267070376 Text_07
    5052267070376 Text_08
    5052267070376 Text_09
    5052267070376 Text_10

    Thanks so much
    Last edited by mjd128; 04-18-2014 at 02:33 PM.

  5. #5
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: help with an excel function

    Easily done, but please comply with the request to change the thread title first.

  6. #6
    Registered User
    Join Date
    04-18-2014
    Location
    Surrey
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    sorry, was difficult to know the correct question to ask?

  7. #7
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    Try this in B2, copied down:

    =IF(A2=A1,"Text_0"&COUNTIF($A$1:A2,A2),"Text_01")

    It would be neater if you could manage without that leading 0, though:

    =IF(A2=A1,"Text_"&COUNTIF($A$1:A2,A2),"Text_1")

  8. #8
    Registered User
    Join Date
    04-18-2014
    Location
    Surrey
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    Thank you so much, that is fantastic and has saved me a lot of searching

    At present I think I am a guest, how can I become a full member, I feel obliged to do this with the work you have saved me

    Is there a joining fee?

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    @mjd128
    You are a member of the forum as you have registered. You could not post as a guest. There is no fee. Please read over our rules to have a better understanding of how the forum operates.

    Welcome to the forum.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  10. #10
    Registered User
    Join Date
    04-18-2014
    Location
    Surrey
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    OK many thanks for all your help

  11. #11
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    Quote Originally Posted by mjd128 View Post
    OK many thanks for all your help
    You are welcome!

  12. #12
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    Hello,

    In order to work that formula, column A items must be grouped together. ie: If you enter 5052267069936 in A17 formula returns Text_1, but as this 2nd occurrence must be Text_2?

    This version will work grouped or ungrouped data. B1 then copied down.

    ="Text_"&TEXT(COUNTIF(A$1:A1,A1),"00")
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  13. #13
    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. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: What Excel function would you use to recreate an ever in creasing number of text Resul

    Quote Originally Posted by Haseeb A View Post
    ="Text_"&TEXT(COUNTIF(A$1:A1,A1),"00")
    A nice improvement!

+ 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. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  2. Excel Match Function not working with Right function.
    By swap579579 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2013, 09:00 PM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Replies: 2
    Last Post: 03-30-2009, 04:04 AM
  5. Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1