+ Reply to Thread
Results 1 to 7 of 7

Export columns based on alphabetical order

  1. #1
    Registered User
    Join Date
    02-26-2016
    Location
    Stateline, NV
    MS-Off Ver
    2013
    Posts
    6

    Export columns based on alphabetical order

    Ok so probably the most NEWB question on this forum, but I can't think of a way to search correctly to get the answer I need.

    So We have a master list of employees First Name, Last Name, Employee Number, Address and so on. What I need to do is create additional "sheets" inside the excel document that sort the employees based on last name.

    All the A's on sheet2, B's on sheet3 etc. I'm positive there is a (Select * FROM Master WHERE LastName LIKE "A%") type excel function, but I can't for the life of me figure out where to start. Please help!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,847

    Re: Export columns based on alphabetical order

    Hi rpetty,

    I'd suggest to not put your master on separate sheets. You can do an AutoFilter on the master sheet and not duplicate your data. If you copy it to more places it becomes less useable for you. Leave it all in a single big table and make sure the info is correct there.

    https://support.office.com/en-us/art...6-27B932E186E0

    Look at "Begins With" on:
    http://www.contextures.com/xlautofilter01.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-26-2016
    Location
    Stateline, NV
    MS-Off Ver
    2013
    Posts
    6

    Re: Export columns based on alphabetical order

    The data is generated bi-monthly so long term data retention is not a problem. This is to sort alphabetically to create mailing lists for sending out payroll checks. Before they are mailed they're checked against the master record twice to make sure they are complete. This is just a sorting tool.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Export columns based on alphabetical order

    This is a customer is always right type reply, because I agree with Marvin that this is more complicated than need be.
    This proposed solution employs an array entered formula* as well as variations on a regular formula.
    There is a chance, pretty good depending on the number of employees, that you'll find some last names duplicated, therefore the following array entered formula will index the list of employee numbers, which I assume to be unique values:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The basic formula that populates the rest of the information is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-06-2016
    Location
    India
    MS-Off Ver
    2007
    Posts
    72

    Re: Export columns based on alphabetical order

    Quote Originally Posted by JeteMc View Post
    This is a customer is always right type reply, because I agree with Marvin that this is more complicated than need be.
    This proposed solution employs an array entered formula* as well as variations on a regular formula.
    There is a chance, pretty good depending on the number of employees, that you'll find some last names duplicated, therefore the following array entered formula will index the list of employee numbers, which I assume to be unique values:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The basic formula that populates the rest of the information is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear ar
    ound your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    How to add

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Export columns based on alphabetical order

    Best advice I could give is to make 23 more sheets, Label them and then copy from one of the existing sheets and paste to the new sheets.
    Remember to change the letter in the array formula to match the sheet.
    Also remember, after changing the letter, to simultaneously press the Ctrl, Shift and Enter keys before copying down.
    Let us know if you have any questions.
    (Please do not quote entire posts, if there is a question about a specific part just quote that part, however for in general quoting is not needed)

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Export columns based on alphabetical order

    This may help a bit. I placed a formula in cell H1 that will display the sheet name. I then changed the array entered formula to reference cell H1. That means that you can make your 26 sheets, one for each letter of the alphabet, then copy the range of cells, A:H and as far down as needed, from your first sheet, presumably sheet A and paste to cell A1 of each subsequent sheet. If you do this changes to the array entered formula will not need to be made after each copy.
    Note: Select to paste keeping column widths.
    The formula for cell H1 is: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Export specific columns in different order without header to csv
    By w2kbug in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2014, 04:42 AM
  2. 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. sorting a column based on a predetermined order (not alphabetical)
    By MonkeyFlyer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 10:34 AM
  5. Replies: 0
    Last Post: 04-01-2011, 12:10 PM
  6. Alphabetical order possible? or not
    By OSSIE in forum Excel General
    Replies: 5
    Last Post: 06-22-2006, 02:25 PM
  7. Alphabetical Order
    By Jennifer_Taylor in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-27-2005, 10:49 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1