+ Reply to Thread
Results 1 to 2 of 2

Ordering states in a pivot table so that United States appears at the bottom

  1. #1
    Registered User
    Join Date
    02-06-2009
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    49

    Ordering states in a pivot table so that United States appears at the bottom

    I have a pivot table where I list individual cash receipts for farm commodities. I want the rows to be sorted alphabetically by state except that I want to move the United States row to be positioned at the bottom of each commodity listing. When I use just the state names, the United States is listed after Texas. My workaround is to convert the U.S. to Z United States in the state names. I have the FIPS codes (P.O. numbers for states) but I don't want to use them without the state names. When I use both FIPS and state names, I get 2 rows per state, FIPS and then state name, and I need only one. I tried concatenating the FIPS code, as text, and the state name but I got a very unusual sort. The states were in the order: 01 Alabama, 10 Delaware, 12 Florida,...02 Alaska, 20 Kansas, etc. Is there any way to put the FIPS code and the state name on the same line? I have attached a file that shows what I am trying to do.
    Attached Files Attached Files
    Last edited by cmcgath; 12-30-2014 at 05:30 PM. Reason: Solved

  2. #2
    Registered User
    Join Date
    02-06-2009
    Location
    Washington, DC, USA
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Ordering states in a pivot table so that United States appears at the bottom

    I finally remembered that spaces sort before anything else and put a space before the single-digit FIPS codes. They now sort correctly.

+ 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. [SOLVED] International and United States custom cell formatting?
    By vamosj in forum Excel General
    Replies: 4
    Last Post: 06-28-2013, 10:54 AM
  2. Replies: 4
    Last Post: 04-13-2013, 06:19 AM
  3. [SOLVED] Reference for States
    By JimDandy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2013, 06:38 PM
  4. Help Organizing States
    By KRayKeeling in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2008, 10:34 AM
  5. US states
    By Tammy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2006, 11:50 AM

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