+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 21

Reversing the Order of Words in Columns

  1. #1
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Microsoft Excel Starter 2010, Microsoft Office version 14.0
    Posts
    19

    Reversing the Order of Words in Columns

    Hi,

    I was wondering if anyone would be able to help me. I use is Microsoft Excel Starter 2010 (Microsoft Office version 14.0).

    The only column I am using is "Column A" in my excel document. It is filled with approximately 20,000 names.

    The names look like this:

    F Bloggs
    B Jones
    S Brown
    F Smith

    What I want to do is reverse the order so they look like this:

    Bloggs F
    Jones B
    Brown S
    Smith F

    Any help would be most greatly appreciated.

    Thanks and kind regards,

    Paul.
    Last edited by PaulBollard; 01-13-2017 at 11:39 AM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365/2016
    Posts
    7,804

    Re: Reversing the Order of Words in Columns

    Enter formula in B1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 F Bloggs Bloggs F
    2 B Jones Jones B
    3 S Brown Brown S
    4 F Smith Smith F
    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

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: Reversing the Order of Words in Columns

    Here's another one...

    Data Range
    A
    B
    1
    F Bloggs
    Bloggs F
    2
    B Jones
    Jones B
    3
    S Brown
    Brown S
    4
    F Smith
    Smith F


    This formula entered in B1 and copied down:

    =MID(A1,FIND(" ",A1)+1,20)&" "&LEFT(A1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    You could use something like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If by chance there is a name instead of an initial this version will handle both single given names and Single initials
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Capture.JPG
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Microsoft Excel Starter 2010, Microsoft Office version 14.0
    Posts
    19

    Re: Reversing the Order of Words in Columns

    Thank you very much to all three of you who replied. All three solutions worked so thanks very much indeed.

    Even though all three worked, I came across a couple of minor problems so I would like to ask two additional questions that would help me further.

    (1) I tried arranging the information which is now in Column B into alphabetical order (clicking on Sort & Filter, Sort A to Z) but it stayed in the same order.

    (2) A few hundred of the names consisted just of a single name (so the order didn't need rearranging). For all these names, it just says #VALUE! in Column B.

    Like before, any information regarding these queries would be greatly appreciated.

    Regards, Paul.

  6. #6
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    The reason that the order doesn't change is that the formulas always refer back to the original list. If you want alphabetic order you could simply copy and paste values the results of the formula used in place to remove the formulae or copy to another location and simply sort into order.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: Reversing the Order of Words in Columns

    Quote Originally Posted by PaulBollard View Post
    A few hundred of the names consisted just of a single name (so the order didn't need rearranging). For all these names, it just says #VALUE! in Column B.
    Try this one...

    Data Range
    A
    B
    1
    F Bloggs
    Bloggs F
    2
    B Jones
    Jones B
    3
    S Brown
    Brown S
    4
    F Smith
    Smith F
    5
    Kruzic
    Kruzic
    6
    P Balik
    Balik P
    7
    Tronzo
    Tronzo


    This formula entered in B1 and copied down:

    =IFERROR(MID(A1,FIND(" ",A1)+1,20)&" "&LEFT(A1),A1)

  8. #8
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    The reason that the order doesn't change is that the formulas always refer back to the original list. If you want alphabetic order you could simply copy and paste values the results of the formula used in place to remove the formulae or copy to another location and simply sort into order.
    This will return single names from the list. Enter in B2 and fill down
    =IF(A2="","",IFERROR(TRIM(MID(A2,FIND(" ",A2),25)&" "&LEFT(A2,FIND(" ",A2))),A2))
    This however will get the list into alphabetic order for you
    Enter this in C2 and fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter this in D2 and fill down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Data Range
    A
    B
    C
    D
    1
    Original
    Reversed
    Order
    Alphabetic order
    2
    F Bloggs
    Bloggs F
    3
    Able
    3
    Bob Jones
    Jones Bob
    1
    Bloggs F
    4
    S Brown
    Brown S
    2
    Brown S
    5
    Fred Smith
    Smith Fred
    0
    Jones Bob
    6
    Able
    Able
    4
    Smith Fred
    7
    8
    =COUNTIF($B$2:$B$20,">"&B2)
    9
    =IFERROR(INDEX($B$2:$B$20,MATCH((ROWS($D$2:D2)-1)*-1+MAX($C$2:$C$20),$C$2:$C$20,0)),"")

  9. #9
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Microsoft Excel Starter 2010, Microsoft Office version 14.0
    Posts
    19

    Re: Reversing the Order of Words in Columns

    Thanks very much to the two of you who replied to my latest post.

    I no longer have the problem with single names showing up as #VALUE! which is great!

    However, having tried the two formulas you suggested newdoverman for Columns C and D, there are still problems.

    (1) Firstly, the list in column D consists of only 8 names.
    (2) The 8 names listed are the top 8 different names from my list in column B, and only those names have been rearranged into alphabetical order in Column D.
    (3) A lot of the names in columns A and B are duplicate names and it's only listed these once in column D.

    Also, I have another excel document that looks something like this:

    Column A...................Column B
    F Bloggs.....................Red hair
    Bob Jones...................Yellow Tie
    S Brown.....................Black briefcase
    Fred Smith..................Blue Eyes
    Able...........................Silver Cufflinks

    For this particular excel document, how could I do exactly the same thing (i.e. reversing the names in column A and arranging the new list in alphabetical order) yet making sure what is written in Column B appears alongside the correct name?

    Any help would be gratefully received and much appreciated.

    Paul.
    Last edited by PaulBollard; 01-11-2017 at 10:19 AM.

  10. #10
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    Point 1) There never were 8 names in the example list. Only 5 names were given.

    Point 2) I do not understand this point at all.

    Point 3) There was no mention of duplicated names in column A so no provision was made for that. Column B is derived from column A so if there are duplicates in column A they will also appear in column B.

    Here is a guess at what you want...added columns and all. Column A has duplicated names, Column B has Other Data for the names, the other columns are basically the same as before only taking into account the added data and the length of the columns.
    Attached Files Attached Files

  11. #11
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    This file is more efficient, I believe.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Microsoft Excel Starter 2010, Microsoft Office version 14.0
    Posts
    19

    Re: Reversing the Order of Words in Columns

    Thanks very much for your reply newdoverman.

    I'm probably going to sound quite stupid now but I'm really struggling to open or save the file. I have Windows 10.

    I click on the file and something comes up saying: Do you want to open or save attachment.php (9.78KB) from excelforum.com?

    So I click on 'Open'. I think the first time I tried to open it, it asked me what program I wanted to open it in (and didn't have a clue) so I took a plunge and chose Internet Explorer. Now everytime I try to open the file, it opens it (or tries to open it) using Internet Explorer. I'm not sure how to reverse this - or if I was able to reverse this, what program to open it in instead.

    Anyway, upon clicking on 'Open', a message comes up saying: What do you want to do with attachment.php? I click on "Open".

    Then the "View Downloads - Internet Explorer" window comes up. So I click on 'save' and then 'save as' from the drop-down menu. I save it in my Downloads folder. I then click on the file in my downloads folder and the "View Downloads" menu comes up again......like a vicious circle. It asks me: "Do you want to save this file?".

    Obviously I'm doing something fundamentally wrong and probably sound pretty stupid. Do you know what it is I am doing wrong?

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: Reversing the Order of Words in Columns

    Quote Originally Posted by PaulBollard View Post
    I click on the file and something comes up saying: Do you want to open or save attachment.php (9.78KB) from excelforum.com?
    That's some kind of bug with the forum software. It's been around for several days now.

    Choose Save, then choose Save As...

    Save it with an Excel file name/extension. Filename.xlsx

  14. #14
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Microsoft Excel Starter 2010, Microsoft Office version 14.0
    Posts
    19

    Re: Reversing the Order of Words in Columns

    Thanks very much for that advice Tony. I managed to open the documents straight away after that.

    Thanks for the two attachments newdoverman,

    I'm still having problems achieving my objectives. I think it's because I've explained myself very badly so I'll try to explain things a bit better......possibly from the beginning.

    I have a excel document filled with 20,000+ names. They consists of: (i) just a single name, (ii) initialled names, (iii) two or more names, (iv) duplicates.

    I copied and pasted these 20,000+ names into Column A of your List2.xlsx excel document and only the top 8 different names appeared in Column D.

    I think this is perhaps the part that I explained very badly. Whereas you thought I was referring to the list of 5 names that were used as an example (F Bloggs, Bob Jones, S Brown, Fred Smith, Able) I was referring to the 20,000+ names that I copied and pasted from another excel document.

    What is happening is: (1) only the names that appear on rows 2-20 are appearing in Column D of the List2.xlsx document.

    That's why I mentioned before that only 8 different names appeared in Column D. It's because only 8 different names appeared on rows 2-20 out of my list of 20,000+.

    (2) The duplicated names that appear on rows 2-20 are only appearing in Column D once. I would like all duplicated names to appear in Column D as many times as they appear in Column A.

    Sorry for any confusion that I caused. I should have made it clear that I want to keep duplicates.

    Hopefully, I've done a better job of explaining myself this time around.

    Regards, Paul.

  15. #15
    Forum Guru newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,328

    Re: Reversing the Order of Words in Columns

    Did you fill the formulae down the columns to accommodate all of your data? Unless you did that, you will get only partial results. The formulae as written will accommodate 25000 rows. I didn't copy the formulae down very far...just enough for you to see that they work. You will have to fill the formulae down if you haven't already.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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