Help with "Sorting" formula?

[ctsui7]

Hi All,

First time posting here, and am hoping the experts here would be able to lend a hand!

I've included a copy of what the data "sort of" looks like, and what the desired output is.

Pretty much I have Data that sorts people based on gender and position.

The thought would be to "Sort" them into a new table based on their gender/position.

So if it was
Employee A, Male, Waiter
Employee B, Female, Waiter
Employee C, Male, Cook
Employee, D, Female, Cook
Employee E, Female, Waiter

My result set would look like:

Male Waiters:

Male Waiters	Male Cooks	female Waiters	female Cooks
Employee A	Employee B	Employee C	Employee D
		Employee E

It seems simple enough but can't seem to get any forumla to work. Would anyone be able to lend a hand? :P

Much appreciated!

[Ditto621]

Pivot Table Book 1 CTSUI7.xlsx

Would a pivot table work here? I don't know if you're looking for something formula driven so that you can further manipulate the data, but if it's just straight sorting into another table, I think pivot tables work pretty well. See attached.

[ctsui7]

Thanks Ditto,

I think the Pivot would be closer to a "last" step.

I think Ideally if I can get some sort of formulaic solution that would be most preferable

[vlady]

maybe array formula -ctrl+shift+enter

=IFERROR(INDEX($D$2:$D$11,SMALL(IF($C$2:$C$11="Waiter",IF($B$2:$B$11="m",ROW($D$2:$D$11)-ROW($D$2)+1)),ROWS($A$1:$A1))),"")

[Tony Valko]

Try this...

Enter these column headers:

F1:I1 = M, M, F, F
F2:I2 = Waiter, Cook, Waiter, Cook

Enter this array formula** in F3:

=IFERROR(INDEX($D:$D,SMALL(IF($B$2:$B$11=F$1,IF($C$2:$C$11=F$2,ROW(C$2:C$11))),ROWS(F$3:F3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to I3 then down until you get a row full of blanks.

Here's your file with this implemented:

Book1(1).xlsx