# Help needed on sorting formula

#### stuckagain22

##### Board Regular
I am trying to get the following formula to alphabetically sort data:

Code:
``=INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))``

Cell A1: pear
Cell A2: apple
Cell A3: orange

cells A1:A3 are named as: data

The formula above results in: #N/A

Thanks.

Thanks.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### venkat1926

##### Well-known Member
why not just sort in the worksheet or in vba.

see help in worksheet and vbeditor.

the macro in vba will be something like this

Code:
``````Sub test()
Range("data").Sort key1:=Range("data"), Order1:=xlAscending

End Sub``````

#### stuckagain22

##### Board Regular
why not just sort in the worksheet or in vba.

It has to be auto sorted -- this means the user of the sheet does not have to do anything... including using a macro.

Thanks, but that is where the formula is from... and it doesn't work.

#### pgc01

##### MrExcel MVP
Hi stuckagain22

Thanks, but that is where the formula is from... and it doesn't work.

Yes it does. As it's said in the link this is a multi-cell array formula.
You have to follow the instructions exactly:

- Select the a number of cells (in the example C2:C21)
- type (or paste) the formula in the formula bar
- confirm with Ctrl+Shift+Enter (and not just Enter).

Hope this helps
PGC

#### Peter_SSs

##### MrExcel MVP, Moderator
edit: Woops, way too slow - didn't see your post pgc01
Thanks, but that is where the formula is from... and it doesn't work.
Try again, noting the first part of this instruction from the page:
Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).

Replies
1
Views
107
Replies
4
Views
256
Replies
3
Views
508
Replies
5
Views
206
Replies
5
Views
208

1,190,790
Messages
5,982,928
Members
439,807
Latest member
WXM86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back