Create a sorted list from an unsorted list in excel using if and array

Question

I got stuck trying to do some organisation in my excel sheet. Hope someone can help me. Thank you.

I know how to use a nested if statement for an array but there's something I am not able to figure out.

I have an unsorted list:

No | ITEM   | Cost | Category 
#1 | item 1 | $10  |    A
#1 | item 2 | $30  |    C
#2 | item 3 | $70  |    A
#3 | item 4 | $20  |    B
#4 | item 5 | $50  |    B

I want to sort them into 3 seperate list on the right side of the excel sheet. Category A, B and C

Category A

ITEM   | Cost
item 1 | $10
item 3 | $70

Category B

ITEM   | Cost
item 4 | $20
item 5 | $50

Category C

ITEM   | Cost
item 2 | $30

What I did initially was to use an array in a nested if statement on the individual cell in the sorted list.

For example,

 =IF(D1 : D5="A", B1, "")

But it doesn't sort out properly and display error. I have hundreds over items and it is a bit hard to go through them everytime I want to look up something. The list cannot be sorted using the "sort" in excel because I want the unsorted list to be arranged by dates.

Sorry for the trouble, I just need someone to teach me how to do it one of the sorted category and I should figure out the rest on my own. thank you.

Answer 1

Use the following array formula in F3 cell as below screenshot. Then drag right 1 cell and drop below as required. For Category B You have to enter same formula in I3 cell just changing A to B.

=IFERROR(INDEX(B$2:B$6,SMALL(IF($D$2:$D$6="A",ROW($D$2:$D$6)-ROW($D$1),""),ROW(1:1))),"")

Note: You must press CTRL+SHIFT+ENTER after typing formula.