none
Need to match and sort data from multiple columns

    Question

  • Hello,

    here is what I have: a datasheet where column A contains 4,000 Customer Names, and column B contains their related Customer IDs.  Adjacent I have four additional columns each containing Customer IDs: the number of IDs in these columns is less than column B, eg 500, 2,000, etc; they don't follow the same sorting order as column B; and a subset of those IDs matches the IDs in column B, while other IDs may not exist in column B. 

    Here's what I need: I am interested in seeing on the same row the IDs that match across columns so that i can easily see the Customer Name they relate to.  

    Progress made so far: Through an INDEX and MATCH function I was able to create columns next to the above four columns to match up the names from column A to the IDs in Column C, etc. via IDs in Column B. That works beautifully because i can filter and see the list of Customer Names for each column; however a drawback is that because the four columns contain a different set of data/different lenght, they are unsorted and it's difficult to see same Customer IDs and map them to Customer Name.

    Is there a specific function i should use? thanks for your help!

    -angie

     


    angi
    I am adding a view of what i have, followed by desired outcome. Thank you.

     

    4,000 Customer Name 4,000 Customer IDs 1,900 Customer IDs 800 Customer IDs 600 Customer IDs 200 Customer IDs
    Alex 2222 4444   7777  
    Lois 7777 8888      
    Mark 1111   1111    
    Sarah 3333       9999
    Maya 8888 1111 5555 3333 2222
               
               
    4,000 Customer Name 4,000 Customer IDs 1,900 Customer IDs 800 Customer IDs 600 Customer IDs 200 Customer IDs
    Alex 2222       2222
    Lois 7777      7777  
    Mark 1111 1111 1111    
    Sarah 3333     3333  
    Maya 8888 8888      
          5555    
              9999

    Monday, May 16, 2011 1:05 AM

Answers

  • Hi,

    What's the Excel version do you use? Excel 2010 or 2007, Try using the Sort dialog box to sort on multiple columns can make it easier to find the data you need.

    In the Sort dialog box, click the Options button, choose Sort Left to Right, and click OK. follow these steps:

    1 Select or click in the list of data you want to sort.You can select only a single column of data if you want to sort that column independently of the rest of the data. However, in most cases you are likely to sort the entire list.

    2 Click the Sort button in the Sort & Filter group on the Data tab.The Sort dialog box appears. If your data includes column headings, make sure the My Data Has Headers option is checked. Excel usually detects the correct setting automatically.

    3 From the Sort By drop-down list, select the column by which you want to sort.This drop-down list includes the column headings for each column in the list.

    4 From the Sort On drop-down list, choose Values.Notice that you also can perform the sort on Cell Color, Font Color, or Cell Icon. Typically, you will perform a sort based on values (rather than formatting).

    5 From the Order drop-down list, select how you want to sort the data.The options that appear in this list change based on the contents of the sort column. Choose A to Z or Z to A to sort text values, Smallest to Largest or Largest to Smallest to sort numeric data, or Oldest to Newest or Newest to Oldest to sort by dates.

    6 Click the Add Level button.Additional drop-down list boxes appear for the secondary sort column. This is the column Excel will sort by if two or more items are identical in the first Sort By option.

     
    7 Repeat steps 3 through 5 for the new sorting level.After you specify the options for the secondary sort column, you can add more sort columns as needed. Use the Move Up and Move Down buttons if you decide to change the order of the sort columns.

    8 Click OK.The dialog box closes and Excel performs the sort process.

    Sincerely

    Harry

    Tuesday, May 17, 2011 6:45 AM
    Moderator

All replies

  • Hi,

    What's the Excel version do you use? Excel 2010 or 2007, Try using the Sort dialog box to sort on multiple columns can make it easier to find the data you need.

    In the Sort dialog box, click the Options button, choose Sort Left to Right, and click OK. follow these steps:

    1 Select or click in the list of data you want to sort.You can select only a single column of data if you want to sort that column independently of the rest of the data. However, in most cases you are likely to sort the entire list.

    2 Click the Sort button in the Sort & Filter group on the Data tab.The Sort dialog box appears. If your data includes column headings, make sure the My Data Has Headers option is checked. Excel usually detects the correct setting automatically.

    3 From the Sort By drop-down list, select the column by which you want to sort.This drop-down list includes the column headings for each column in the list.

    4 From the Sort On drop-down list, choose Values.Notice that you also can perform the sort on Cell Color, Font Color, or Cell Icon. Typically, you will perform a sort based on values (rather than formatting).

    5 From the Order drop-down list, select how you want to sort the data.The options that appear in this list change based on the contents of the sort column. Choose A to Z or Z to A to sort text values, Smallest to Largest or Largest to Smallest to sort numeric data, or Oldest to Newest or Newest to Oldest to sort by dates.

    6 Click the Add Level button.Additional drop-down list boxes appear for the secondary sort column. This is the column Excel will sort by if two or more items are identical in the first Sort By option.

     
    7 Repeat steps 3 through 5 for the new sorting level.After you specify the options for the secondary sort column, you can add more sort columns as needed. Use the Move Up and Move Down buttons if you decide to change the order of the sort columns.

    8 Click OK.The dialog box closes and Excel performs the sort process.

    Sincerely

    Harry

    Tuesday, May 17, 2011 6:45 AM
    Moderator
  • Thanks Harry,

    i'm using Excel2010. When i follow the steps above get stuck on step #2 as the Sort dialog box does not show me the option to Sort by Columns but it forces a Sort by Rows. I can only select one row at a time; if I select More rows at the bottom of the scroll list, Excel will highlight the entire list, which is good, but on OK it errros with "More than one row has been selected. Select one row or a cell within that row".

    Any ideas on how to work around this? thanks!


    angi
    Saturday, May 21, 2011 3:59 PM
  • Hi,

    There are chances of multiple cells selected or region of cells selected when you click on one cell.

    There are some reasons:

    when Excel is in the ‘extend mode’. Press Esc or F8 once to turn off the Extend mode in Excel.

    when view in page layout view, switch back to normal view.

    Regards,

    Harry

     

    Monday, May 23, 2011 8:53 AM
    Moderator
  • Hi Harry,

    Extend mode is off and Workbook Views is set to Normal. I am however still getting the same results: Excel wants to sort by Row after step 2 above is complete.  Anything else i should try? thanks! 


    angi
    Monday, May 23, 2011 11:22 PM
  • Hi,

    Sometimes it depends on GUI (Graphic user interface) conflicts and at times it will not be compatible with excel application.

    Sincerely

    Harry

    Tuesday, May 24, 2011 2:06 AM
    Moderator
  • HI Harry,

    does this mean that i cannot do the Sort operation for this file?  Should i email you the xls? thanks.


    angi
    Tuesday, May 24, 2011 2:43 AM
  •  

    Hi,

     

    Thank you for your question.

     

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

     

    Thank you for your understanding and support.

     

    Best Regards,

     

    Harry Yuan

    Tuesday, May 24, 2011 9:23 AM
    Moderator
  • Hello Angela,

    I believe I understand what you are trying to do, but could you send me your workbook or a sample spreadsheet to willbu@microsoft.com?

    I'd like to take a look at what you are doing.

    Thanks,

    Will Buffington
    Microsoft Excel Support

    Tuesday, May 24, 2011 7:58 PM
    Moderator
  • I am having a very similar problem, where I have a set of columns that includes a database entry, an adjacent column with a description of the database entry, and additional adjacent columns with quantitative data related to.  I have 9 additional sets of columns with the same set of headings, but they do not neceassarily contain the same set of database entries, descriptions, etc.  How can I sort so that the database entries, descriptions, and quantitative data are aligned across all 10 sets of data?

    Wednesday, April 11, 2012 2:49 PM