How to separate data in one column into two colums based on data in other column in Excel

Brian Lambert 1 Reputation point


I need to separate number the numbers in one column (Column A) based on the identifiers in another column (Column B). In the attached example, I need all of the identifiers in Column B to be separated out and placed into new columns. For example, Type A numbers need to be in one column and all of the Type B numbers in another column.

How would I go about doing this?

Thank you for any help that you can provide!



Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,461 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Herbert Seidenberg 1,191 Reputation points
    0 comments No comments

  2. Emily Hua-MSFT 27,526 Reputation points

    Hi @Brian Lambert ,

    You could try the Index and Match function.

    Enter the formula =IFERROR(INDEX($A$1:$A$6,SMALL(IF($B$1:$B$6=D$1,ROW($1:$6),4^8),ROW(1:1)))&"","") in Cell D2 on my sample.
    As this formula is an array formula, I need to press Ctrl + Shift + Enter.
    Then drag down and right.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.