Using an array formula to sort alphabetically over multiple columns

[AliGW]

I've often found answers to queries here, but not posted until today. I'm looking for some help with an array formula, having started using these only two days ago.

In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.

This is the array formula I am using at the moment:

Please Login or Register  to view this content.

Is anyone able to help me adapt the attached workbook to work in the way I would like, please?

Thanks in advance.

[chinraj]

Please have a look on the below thread and come back,
http://www.excelforum.com/excel-gene...-formulas.html

[hemesh]

Hello and welcome ! you need to use your main results which you have generated as helper and based on that you can apply another formulaand sort the list ! Find attached

you can hide your main results afterwards

In Cell A1 write French
copy paste below in B2 then drag down and drag to right(Array entered)

=IF(ISERROR(INDEX(Options!$A$4:I98,SMALL(IF(Options!$H$4:$H$98=$A$1,ROW(Options!$H$4:$H$98)),ROW(1:1))-3,COLUMN(A1))),"",INDEX(Options!$A$4:I98,SMALL(IF(Options!$H$4:$H$98=$A$1,ROW(Options!$H$4:$H$98)),ROW(1:1))-3,COLUMN(A1)))

[AliGW]

@hemesh - Many thanks for trying to help, however your suggested answer is ordering the columns individually. Look again at the example workbook you posted - first names are with the incorrect surnames and forms. I should have said that I had already discovered how to order one column alphabetically using the formula you proposed, but it's multi-column ordering that I need to do.

@chinraj - Thank you, but I don't see how the thread you suggested links to my exact problem.

[XOR LX]

Hi,

You don't say on which field the alphabetic sorting should be based. Surname, I presume?

Regards

[AliGW]

Hi,

You don't say on which field the alphabetic sorting should be based. Surname, I presume?

Regards

Yes - surname, then first name (as there may be more than one with any given surname). Thanks for looking!

[XOR LX]

Thanks. I notice your profile says 2010 yet you are currently using a construction (IF(ISERROR)) which seems to suggest that this needs to be compatible with 2003 - is this correct? If not, we can simply somewhat with IFERROR().

Regards

[hemesh]

Try this
=INDEX(B$2:B$100,MATCH(SMALL(IF(COUNTIF($B$2:$B$100,"<="&$B$2:$B$100)>0,COUNTIF($B$2:$B$100,"<="&$B$2:$B$100)),ROW(A1)),COUNTIF($B$2:$B$100,"<="&$B$2:$B$100),0))

Array ENtered f2 drag down and to right

[AliGW]

Thanks. I notice your profile says 2010 yet you are currently using a construction (IF(ISERROR)) which seems to suggest that this needs to be compatible with 2003 - is this correct? If not, we can simply somewhat with IFERROR().

Regards

No, it does not need to be compatible with 2003 - I'm using 2010 at work and at home. Thanks!

[AliGW]

Try this
=INDEX(B$2:B$100,MATCH(SMALL(IF(COUNTIF($B$2:$B$100,"<="&$B$2:$B$100)>0,COUNTIF($B$2:$B$100,"<="&$B$2:$B$100)),ROW(A1)),COUNTIF($B$2:$B$100,"<="&$B$2:$B$100),0))

Array ENtered f2 drag down and to right

Nearly there, thank you! I need the columns ordered by surname first, then first name. This, I find, works:

Please Login or Register  to view this content.

Problem solved - thanks again!!!

[hemesh]

Check This here is the sorted version of options ! in second sheet you can create formula based on sorted

[AliGW]

Check This here is the sorted version of options ! in second sheet you can create formula based on sorted

Thank you, but I have solved the issue by tweaking the formula you suggested in your previous post. I did not want the sorting to happen on the Options tab, anyway.

[AliGW]

Thanks. I notice your profile says 2010 yet you are currently using a construction (IF(ISERROR)) which seems to suggest that this needs to be compatible with 2003 - is this correct? If not, we can simply somewhat with IFERROR().

Regards

You learn something new every day - thanks again!

[hemesh]

Hello ! I realized a little bit later that the formula won't be fetching if there is any duplicate entry in surname ! But here is the modified version. find attached.
on sets sheet is the new formula and on options sheets the formula is earlier one. You can see the difference.

[AliGW]

Hello ! I realized a little bit later that the formula won't be fetching if there is any duplicate entry in surname ! But here is the modified version. find attached.
on sets sheet is the new formula and on options sheets the formula is earlier one. You can see the difference.

Thanks again. This is driving me nuts! I want to have the first name column before the surname column, and I am very carefully copying and tweaking the formula, but I can't get it to work.

[hemesh]

You can just change the heading of surname to first names In new post with name fetching duplicate data also

=IFERROR(INDEX($B$2:$D$100,MATCH(SMALL(IF($A$2:$A$100>0,$A$2:$A$100),ROW(A1)),$A$2:$A$100,0),MATCH(F$1,$B$1:$D$1,0)),"")
red part brings the column to be fetched.

as you can see it matches first Name with First name in another column

[AliGW]

You can just change the heading of surname to first names In new post with name fetching duplicate data also

=IFERROR(INDEX($B$2:$D$100,MATCH(SMALL(IF($A$2:$A$100>0,$A$2:$A$100),ROW(A1)),$A$2:$A$100,0),MATCH(F$1,$B$1:$D$1,0)),"")
red part brings the column to be fetched.

as you can see it matches first Name with First name in another column

Gotcha! That's what I was missing - thanks. Will try this out later and let you know.

[AliGW]

Gotcha! That's what I was missing - thanks. Will try this out later and let you know.

Yes, that was it. I understand the formula now and everything is working as it should. Thank you once again!

[hemesh]

Thanks for the feedback and Rep.