CODEWITHSUNDEEP
excelexcel-formula
user140852
user140852

Reputation: 33

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

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.

Upvotes: 3

Views: 1986

Answers (3)

Sangbok Lee
Sangbok Lee

Reputation: 2229

Alas, it's easy job in Google Spreadsheet since it provides query().

When 1:1 is No, ITEM, Cost, Category, and B:B is ITEM, item1, item2, ..., let's put output in F:K, so F1:G1 is Category, A, H1:I1 is Category, B, and J1:K1 is Category, C. Now input below array formula at F2:G2. (Note that G$1 is 'A')

{=IF($D2=G$1, $B2:$C2, "")}

Now copy F2:G2 and paste at F3:G6. And then copy F2:G6 and paste at H2:K6. Now you can sort F:G for category A, H:I for category B, J:K for category C.

Upvotes: 0

Michael
Michael

Reputation: 4848

Try this formula:

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

Note that this is an array formula for a single cell only. Confirm the formula in a cell in row 2 using CTRL+SHIFT+ENTER and then fill the formula down the remaining cells.

This formula works using the SMALL function. It starts by finding the first lowest ROW number of the D column that contains the value "A". Then when the formula is filled down, you'll next find the second lowest ROW of the D column that contains the value "A" and so on. The Index function returns the value from the Item column at the found ROW number.

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36880

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.

enter image description here

Upvotes: 1

Related Questions