+ Reply to Thread
Results 1 to 21 of 21

Reversing the Order of Words in Columns

  1. #1
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Excel 2212 Build 15928.20216
    Posts
    36

    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
    Posts
    8,903

    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 Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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
    Excel 2212 Build 15928.20216
    Posts
    36

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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 Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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
    Excel 2212 Build 15928.20216
    Posts
    36

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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
    Excel 2212 Build 15928.20216
    Posts
    36

    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 Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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
    Excel 2212 Build 15928.20216
    Posts
    36

    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 Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.

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

    Re: Reversing the Order of Words in Columns

    Why not just reverse the names and from the Data tab click on the Filter button to add filters to each column then sort in order.
    Name List Capture.JPG
    Last edited by newdoverman; 01-12-2017 at 02:08 PM.

  17. #17
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Excel 2212 Build 15928.20216
    Posts
    36

    Re: Reversing the Order of Words in Columns

    Quote Originally Posted by newdoverman View Post
    Why not just reverse the names and from the Data tab click on the Filter button to add filters to each column then sort in order.
    Attachment 496879
    I wasn't aware that Excel Starter 2010 had a Data tab. According to the literature that I've read, it doesn't have one, although that could be wrong.

    Thanks again for your reply. I fully understand where I was going wrong in terms of not filling the formulae down the columns to accommodate all the data. So I've cracked that part.

    It seems like all my problems have now been cracked apart from being able to arrange the reversed names (including duplicates) into alphabetical order.

    Is there a formula you could suggest that would do that for me?

  18. #18
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Excel 2212 Build 15928.20216
    Posts
    36

    Re: Reversing the Order of Words in Columns

    Quote Originally Posted by newdoverman View Post
    Why not just reverse the names and from the Data tab click on the Filter button to add filters to each column then sort in order.
    Attachment 496879
    I wasn't aware that Excel Starter 2010 had a Data tab. According to the literature that I've read, it doesn't have one, although that could be wrong.

    Thanks again for your reply. I fully understand where I was going wrong in terms of not filling the formulae down the columns to accommodate all the data. So I've cracked that part.

    It seems like all my problems have now been cracked apart from being able to arrange the reversed names (including duplicates) into alphabetical order.

    Is there a formula you could suggest that would do that for me?

    Regards,

    Paul.

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

    Re: Reversing the Order of Words in Columns

    Assuming that all your data is across the worksheet like in the examples it would be far more efficient to select all the data including the original list, Click on the Data Tab, Click on the Filter Button to add the filters to the column headers. Click on the header for the Reversed names and sort into order. All the data will be sorted into order. The formulae should still work producing the correct results. You really don't want to sort 20000 records via formula. It would take a long time.

    If you want to sort via formula here is a setup that will go from the original data through to an alphabetic listing including duplicate names. You will have to fill the formulae down to the end of the data or it will not work. I have filled only a few rows which are in yellow in the file.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    01-04-2017
    Location
    Sheffield
    MS-Off Ver
    Excel 2212 Build 15928.20216
    Posts
    36

    Re: Reversing the Order of Words in Columns

    Thank you so much for doing that for me newdoverman. My list of reversed names is now in alphabetical order including duplicates. I'm a very happy man!

    Thanks also for all your help these past few days - you have been so generous. It really means a lot.

    All the best.

    Paul.

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

    Re: Reversing the Order of Words in Columns

    Thank you for giving feedback and the rep.
    Good luck.

+ 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. Reversing data order
    By MT49 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2016, 09:12 AM
  2. reversing the order of a list
    By rookie37 in forum Excel General
    Replies: 2
    Last Post: 11-08-2014, 06:10 AM
  3. Reversing Order of Chart
    By prr in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-02-2009, 05:20 AM
  4. Code for reversing print order
    By Paul Sheppard in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-15-2008, 12:03 PM
  5. Autofill- Reversing Order
    By Earl in forum Excel General
    Replies: 4
    Last Post: 07-31-2006, 05:18 PM
  6. [SOLVED] reversing order of columns of data
    By Jim Brass in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2005, 03:05 PM
  7. [SOLVED] reversing row order
    By Jamie Morken in forum Excel General
    Replies: 1
    Last Post: 05-31-2005, 03: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