+ Reply to Thread
Results 1 to 7 of 7

Vertical character list in alphabetical order - more fine and shorter

  1. 03-03-2015, 09:17 AM #1
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Lightbulb Vertical character list in alphabetical order - more fine and shorter

    Hi Excel Guru's,

    I've formula which making vertical character list in alphabetical order. It works, but I would like to have more fine "code".
    It seems it is not finesse. (for me of course )

    Preconditions:
    1. no vba
    2. all in one formula
    3. without additional "help" columns, etc.
    4. can be placed in any cell

    Please Login or Register  to view this content.
    BTW. If it will take level ZZ the next step will be wrong. I can put 64 times IF function into formula above but this is not what I want.

    Any ideas?

    regards
    sandy
    Reply With QuoteRegister To Reply
  2. 03-03-2015, 09:36 AM #2
    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: Vertical character list in alphabetical order - more fine and shorter

    Can't really say that I have a clear idea of what exactly you trying to do.

    =LEFT(ADDRESS(1,ROWS(A$1:A1),4),LEN(ADDRESS(1,ROWS(A$1:A1),4))-1)&")"
    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
  3. 03-03-2015, 09:43 AM #3
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Vertical character list in alphabetical order - more fine and shorter

    Try

    =SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")&")"
    Last edited by Jonmo1; 03-03-2015 at 09:54 AM. Reason: added the &")"
    Reply With QuoteRegister To Reply
  4. 03-03-2015, 09:47 AM #4
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Vertical character list in alphabetical order - more fine and shorter

    This has been deleted
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    Reply With QuoteRegister To Reply
  5. 03-03-2015, 09:48 AM #5
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Vertical character list in alphabetical order - more fine and shorter

    Hi AlKey,

    Thanks for answer and shorter formula

    Some people (especially management) treat Excel as text editor, i.e. Word. That is why, no more, no less.

    Ups, I was lazy...
    Thank you ALL Jonmo1, sourabhg98
    Last edited by sandy666; 03-03-2015 at 09:53 AM.
    Reply With QuoteRegister To Reply
  6. 03-03-2015, 09:51 AM #6
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Vertical character list in alphabetical order - more fine and shorter

    Try this
    =SUBSTITUTE(ADDRESS(1,ROW(),4),1,"")&")"
    or
    =(LEFT(ADDRESS(COLUMN(),ROW(A697),4,1),SUMPRODUCT(MAX(((26^(ROW($1:$3)))+(26^(ROW($1:$3)-1))<ROW())*ROW($1:$3)))+1)&")")
    Last edited by sourabhg98; 03-03-2015 at 09:58 AM.
    Reply With QuoteRegister To Reply
  7. 03-03-2015, 09:53 AM #7
    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: Vertical character list in alphabetical order - more fine and shorter

    You're welcome and thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).
    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. [SOLVED] Return list of dates by month from a list in alphabetical order
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2015, 03:21 AM
  2. change filter list menu from alphabetical order to order of original list
    By tlacloche in forum Excel General
    Replies: 0
    Last Post: 02-15-2014, 10:30 AM
  3. List in alphabetical order.
    By isameer in forum Excel General
    Replies: 4
    Last Post: 02-13-2013, 07:34 AM
  4. Excel 2007 : Adding new row for a customer list based on alphabetical order
    By kmkelley in forum Excel General
    Replies: 0
    Last Post: 04-01-2011, 12:10 PM
  5. How may I make a list display in alphabetical order in Excel?
    By pat henry in forum Excel General
    Replies: 1
    Last Post: 12-06-2005, 04:15 PM

Tags for this Thread

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