Index Small results sorted in numerical ascending order

sacastiglia

New Member
Hello All,

I am trying to figure out how to sort the results of this formula in ascending numerical order.

=IFERROR(INDEX('Man Hours'!\$B\$5:\$B\$1698,SMALL(IF(('Man Hours'!\$D\$5:\$D\$1698=316001)*('Man Hours'!\$B\$5:\$B\$1698<>316001)*('Man Hours'!\$A\$5:\$A\$1698=\$G\$4),ROW('Man Hours'!\$A\$5:\$A\$1698)-ROW('Man Hours'!\$A\$5)+1),ROWS(\$I\$18:I18))),"")

As always, any assistance is greatly appreciated.

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Domenic

MrExcel MVP
Try...

=IFERROR(SMALL(IF('Man Hours'!\$A\$5:\$A\$1698=\$G\$4,IF('Man Hours'!\$B\$5:\$B\$1698<>316001,IF('Man Hours'!\$D\$5:\$D\$1698=316001,'Man Hours'!\$B\$5:\$B\$1698))),ROWS(\$I\$18:I18)),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

shg

MrExcel MVP
Wouldn't it be if col B were in ascending order?

Last edited:

sacastiglia

New Member
That worked awesome. Hope this is not a misleading post, if it is, I am sorry. But I have names that correspond to the values calculated in the formula above.

I have the following formula but it stops short. Is there a way to do the same thing with the following formula, without eliminating the list name only once even if it is listed on the Man Hours sheet more than once for the same date (multiple jobs in one day).

My numerical values are in column I.

For Instance:

I tried using 'Man Hours'!\$B\$5:\$B\$1698=I18 but it only populates the first three. I think the Frequency function is interfering with the match.

The formula is as follows:

=IFERROR(IF(ROWS(\$J\$18:J18)>\$H\$8,"",INDEX('Man Hours'!\$G\$5:\$G\$1698,SMALL(IF(FREQUENCY(IF(('Man Hours'!\$A\$5:\$A\$1698=\$G\$4+0)*('Man Hours'!\$E\$5:\$E\$1698="LV")*('Man Hours'!\$C\$5:\$C\$1698<>"LV")*('Man Hours'!\$B\$5:\$B\$1698=I18),MATCH('Man Hours'!\$G\$5:\$G\$1698,'Man Hours'!\$G\$5:\$G\$1698,0)),ROW('Man Hours'!\$A\$5:\$A\$1698)-ROW('Man Hours'!\$A\$5)+1),ROW('Man Hours'!\$A\$5:\$A\$1698)-ROW('Man Hours'!\$A\$5)+1),ROWS(\$J\$18:J18)))),"")

Col I Col J

 316101 Jordan Mackenzie C Wellwood 322001 Jorge A Plazola Sepulveda 322001 Servando Chavez Gallardo 322001 There should be a name here 326001 There should be a name here 326001 There should be a name here 326001 There should be a name here

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

MrExcel MVP
Which is the first cell that implements the formula Domenic suggested?

sacastiglia

New Member
I18. The names that correspond to the values calculated in I18 are in J18.

Last edited:

MrExcel MVP

J18, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IF(\$I18="","",INDEX('Man Hours'!\$G\$5:\$G\$1698,
SMALL(IF(('Man Hours'!\$D\$5:\$D\$1698=316001)*('Man Hours'!\$B\$5:\$B\$1698<>316001)*
('Man Hours'!\$A\$5:\$A\$1698=\$G\$4),IF('Man Hours'!\$B\$5:\$B\$1698=\$I18,
ROW('Man Hours'!\$G\$5:\$G\$1698)-ROW('Man Hours'!\$G\$5)+1)),COUNTIF(\$I\$18:I18,I18))))
``````

Last edited by a moderator:

sacastiglia

New Member
You, my friend, are a genius. Thank you sooooo much. Where do I send the reward?

MrExcel MVP
You, my friend, are a genius. Thank you sooooo much. Where do I send the reward?

sacastiglia

New Member
Learning opportunity: I am figuring the COUNTIF(\$I\$18:I18,I18) part of this formula is what puts the values in order. How does this do that</pre>

Replies
5
Views
147
Replies
7
Views
109
Replies
7
Views
371
Replies
5
Views
166
Replies
0
Views
183

1,195,683
Messages
6,011,138
Members
441,587
Latest member
kbsgiri09

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.

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