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

Question

Hello!

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!

Answer 1

Excel 365 Pro Plus with Power Query.
Separate column into custom columns.
https://www.mediafire.com/file/9tq1m0yn6oa714o/01_20_22a.xlsx/file
https://www.mediafire.com/file/w2nlqjp80y7u4fp/01_20_22a.pdf/file

Answer 2

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.

---