Excel Help Needed

ericd102
ericd102 Posts: 2
edited July 2006 in Technical Stuff and Help
I had the answer to this over at RM, but that sit isn't working right now.

I have a column of names in Excel, in the form "first last" or "first middle initial last" (they are mixed, not all names have a middle initial). I need to sort by last name. How can I do this?

Someone (sundevil? maybe?) had created a two-step way (using two forumlas) to do this. The formulas were both pretty complex. One had to be put in one column, and the other in the next column. The second formula referenced the first.


Anyways, this worked perfectly, but I didn't save (like an idiot) and now I can't get it to work because I can't get on RM to get the formulas.

Does anyone know how to do this, or does any RMer have that page still in their history/Tempororay files and could look it up for me? It would be immensley helpful.


Thanks,
Eric
Post edited by Unknown User on

Comments

  • DarkStar
    DarkStar Posts: 734
    you can use the following if the middle initials are followed by a period...

    first we're going to split the first names and any middile initials to their own column...second we'll spilt out the last names to a separate column...third we'll merge the data in both newly created columns so that the last name appears first and is separated from the first name (plus any middle initials) with a coma.

    So the data will start out as
    edward l. severson

    and end up as
    severson, edward l.

    Step 1: To split the first name and middle initials into a cell separate from the last name, copy/paste this formula to whatever column you want the first names plus any middle initials that may exist:
    =IF(ISERROR(FIND(".",A1,1)),LEFT(A1,FIND(" ",A1,1)-1),LEFT(A1,FIND(".",A1,1)))

    Step 2: To split the last names into their own cells, copy/paste this formula to whatever column you want the last name to appear in:
    =IF(ISERROR(FIND(".",A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),RIGHT(A1,LEN(A1)-FIND(".",A1,1)-1))

    Step 3: Time to merge the names again to one cell so that the last name appears first and is separated from the first name (plus any middle initials) with a coma...copy/paste this formula to whatever column you want the merged data to appear in:
    =C1&", "&B1

    ds
    And no one sings me lullabyes
    And no one makes me close my eyes
    So I throw the windows wide
    And call to you across the sky....
  • The Juggler
    The Juggler Posts: 49,598
    Hello nerds,

    I am trying to use the below formula but it's too large, apparently.  I keep getting the below error:

    This formula uses more levels of nesting than you can use in the current file format




    Can anyone tell me what I need to do to shorten it? And there's 20 bucks in it for yourself, if you'll just do the work for me. 



    =IF(B8<6.99,B8*0,IF(B8<7.99,B8*100,IF(B8<8.99,B8*150,IF(B8<9.99,B8*200,IF(B8<10.99,B8*250,IF(B8<11.99,B8*300,IF(B8<12.99,B8*375,IF(B8<13.99,B8*500,IF(B8<14.99,B8*566,IF(B8<15.99,B8*576,IF(B8<16.99,B8*585,IF(B8<16.99,B8*585,IF(B8<17.99,B8*639,IF(B8<18.99,B8*647,IF(B8<19.99,B8*715,IF(B8<20.99,B8*722,IF(B8<21.99,B8*728,IF(B8<22.99,B8*733,IF(B8<23.99,B8*738,IF(B8<24.99,B8*743,IF(B8<25.99,B8*747,IF(B8<26.99,B8*751,IF(B8<27.99,B8*755,IF(B8<28.99,B*758,IF(B8<29.99,B*761,IF(B8<30.99,B8*764,IF(B8<31.99,B8*767,IF(B8<32.99,B8*769,IF(B8<33.99,B8*769,IF(B8<34.99,B8*774,IF(B8<35,B8*776))))))))))))))))))))))))))))))+2500
    www.myspace.com
  • pantelis79
    pantelis79 Posts: 156
    edited January 2019
    The function you posted is missing a parenthesis before "+2500". There are 31 parentheses opening, but only 30 closing.
    Post edited by pantelis79 on
    2006/09/30 Athens, 2007/06/12 Muenchen, 2007/06/13 Katowice, 2007/06/18 London, 2009/08/13 Rotterdam, 2009/08/15 Berlin, 2010/06/30 Berlin, 2010/07/10 Lisbon, 2011/09/03 Alpine Valley, 2011/09/04 Alpine Valley, 2012/07/07 Stockholm, 2012/07/08 Oslo, 2012/07/10 Copenhagen, 2014/06/16 Amsterdam, 2014/06/17 Amsterdam, 2018/06/18 London, 2018/07/01 Prague, 2018/07/03 Krakow, 2018/07/05 Berlin...
  • F Me In The Brain
    F Me In The Brain this knows everybody from other commets Posts: 31,851
    :rofl: 
    The love he receives is the love that is saved
  • The Juggler
    The Juggler Posts: 49,598
    pantelis79 said:
    The function you posted is missing a parenthesis before "+2500". There are 31 parentheses opening, but only 30 closing.
    Thanks. I will add that. It’s still too long though. Can you fix that problem?
    www.myspace.com
  • pantelis79
    pantelis79 Posts: 156
    edited January 2019
    It works in my computer :) . What exactly do you want to do? Could you post a table of values or something like that? I see that "16.99" is repeated in the function - it would be easier to have the initial problem here ;).
    Post edited by pantelis79 on
    2006/09/30 Athens, 2007/06/12 Muenchen, 2007/06/13 Katowice, 2007/06/18 London, 2009/08/13 Rotterdam, 2009/08/15 Berlin, 2010/06/30 Berlin, 2010/07/10 Lisbon, 2011/09/03 Alpine Valley, 2011/09/04 Alpine Valley, 2012/07/07 Stockholm, 2012/07/08 Oslo, 2012/07/10 Copenhagen, 2014/06/16 Amsterdam, 2014/06/17 Amsterdam, 2018/06/18 London, 2018/07/01 Prague, 2018/07/03 Krakow, 2018/07/05 Berlin...

Categories