Formula to sort by numbers

I think VBA would be easier for this. Does each ID always only show up once?

Try ...

in I3

=IFERROR(INDEX($A$2:$A$100,SMALL(IF($E$2:$E$100=I$1,ROW($A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Copy across and down


There appears to be problems on the format of the data: I had to retype some data to get the formula to work


Missed first part of requirement!

Hi,

Does the attached which uses a Pivot Table work for you? It avoids the need for any formulae or VBA so unless the layout you showed is vital then this is a far simpler solution.

Enter formula in I1 and pull it to the right until you see blanks

Formula:

=IFERROR(INDEX($E$2:$E$97,MATCH(0,INDEX(COUNTIF($H$1:H1,$E$2:$E$97),,),)),"")

Then in I3 use this formula

Formula:

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($E$2:$E$97)/(($E$2:$E$97=I$1)),ROWS(I$3:I3))),"")

Pull it to the right and then down

Siumilar approach for Part 1:

=IFERROR(INDEX($E$2:$E$97,MATCH(0,INDEX(COUNTIF($H$1:H1,$E$2:$E$97),0),0)),"")

Drag across.

Part 2:
=IFERROR(INDEX($A:$A,SMALL(INDEX(($E$2:$E$97<>I$1)*10^10+ROW($E$2:$E$97),0),ROWS($1:1))),"")

Drag across and down