Sorting mixed list into two separate lists (INDEX-SMALL-IF)

[nunez100]

Hi,

I am trying to figure out how to separate out a mixed list of staff that are casual or regular, into two separate lists. The first column is the name, and the next is the identifier 'casual' or 'regular'. Example Spreadsheet is attached.

I have used a combination of INDEX with SMALL and IF to do a similar operation in the past, but only because it was explained by the kind people on this forum. I can't get my head around exactly how this combination of functions work together, but I think that it could be done this way.

I was wondering if anyone could help me apply INDEX, SMALL and IF properly to this problem, or perhaps there is a better way?

Thank you in advance.

[CARACALLA]

Data

B4=IFERROR(INDEX('Source Data'!B$3:B$52,AGGREGATE(15,6,ROW('Source Data'!B$3:B$52)-ROW('Source Data'!B$3)+1/('Source Data'!$C$3:$C$52=TRIM(SUBSTITUTE('Desired Output'!$B$3,"Employees",""))),ROWS('Source Data'!$B$3:'Source Data'!B3))),"")

Copy across and down


e4=IFERROR(INDEX('Source Data'!B$3:B$52,AGGREGATE(15,6,ROW('Source Data'!B$3:B$52)-ROW('Source Data'!B$3)+1/('Source Data'!$C$3:$C$52=TRIM(SUBSTITUTE('Desired Output'!$E$3,"Employees",""))),ROWS('Source Data'!$B$3:'Source Data'!B3))),"")

copy across and down

[hrlngrv]

Do you have to do this by formula?

Applying an autofilter to the original table plus the row above it, filter on the 2nd column being Casual, select the filtered records, copy, and paste into the other worksheet. Then switch the filter to Regular and repeat the subsequent steps.

If you have Office 365, you should have the new FILTER function, so you could just enter

Desired Output'!B4:

Please Login or Register  to view this content.

Desired Output'!E4:

Please Login or Register  to view this content.

[nunez100]

Hi,

Thank you so much for this CARACALLA. I'm really sorry, but when I see your solution, I realise that there is a slight change I need to make to the example spreadsheet. I wonder would you mind looking at the updated version?

Apologies for the revision.

Thank you again.

Kind regards

George

[nunez100]

Hi,

Thank you for the filter suggestion, but in this case I think I do need it to be a formula. Thank you

[CARACALLA]

Desidered Output


B4=IFERROR(INDEX('Source Data'!$B$3:$B$52,AGGREGATE(15,6,ROW('Source Data'!$B$3:$B$52)-ROW('Source Data'!$B$3)+1/('Source Data'!$C$3:$C$52="casual"),ROWS('Source Data'!$B$3:'Source Data'!$B3))),"")

Copy down


E4=IFERROR(INDEX('Source Data'!$B$3:$B$52,AGGREGATE(15,6,ROW('Source Data'!$B$3:$B$52)-ROW('Source Data'!$B$3)+1/('Source Data'!$C$3:$C$52="regular"),ROWS('Source Data'!$B$3:'Source Data'!$B3))),"")

Copy down

[nunez100]

Thank you so much!!!!! Working perfectly thanks!

[CARACALLA]

You are welcome