Duplicates sort in order

[JTL9161]

I have a list of names in Column A (A3:A50)
In column B I have various numbers between 1 and 5 for each name
I am trying to sort the entire list (which changes almost daily) with all the 1's first in Col C and then list the associated name from column A in column D, then all the 2's and then 3's and so on till all the 5's at the bottom

There ins't always a set amount for each number in col B. There could be 11 #1's and 9 #2's...etc.


I tried COUNTIF, INDEX and still cannot get it right.

Thanks in advance for your help
James

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	Name
3	Tom Pearce	1	1	Tom Pearce
4	Bill Brewer	3	1	Peter Davy
5	Jan Stewer	5	2	Peter Gurney
6	Peter Gurney	2	2	Dan'l Whiddon
7	Peter Davy	1	3	Bill Brewer
8	Dan'l Whiddon	2	4	Tom Cobley
9	Harry Hawke	5	5	Jan Stewer
10	Tom Cobley	4	5	Harry Hawke
11
Main

Cell Formulas
Range	Formula
C3:D10	C3	=LET(f,FILTER(A3:B50,A3:A50<>""),INDEX(SORT(f,2),SEQUENCE(ROWS(f)),{2,1}))
Dynamic array formulas.

 

[JTL9161]

Thanks...though I am getting a SPILL! error

 

[Fluff]

In that case clear all the cells in C3:D50 & re-enter the formula in C3

 

[JTL9161]

Never mind. I had dragged the formula all the way down. It just stays in top cell. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JTL9161]

Another problem. Can I use an IF statement on the result in col C? I try =IF(C3=1, 100,IF(C3=2,200,IF(C3=3,300,if(C3=4,400,if(C3=5,500, 0))) and it returns and "0". I then tried =IF(F3=1,TRUE,FALSE) it returns a FALSE even though F3 does equal 1.

Suggestions?

 

[Fluff]

Can you post some sample data along with expected results, as I'm not sure what you are asking.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JTL9161]

I did determine that the numbers in Col C are not numbers. I tried a conditional format on the column that if any cell has a "1" then highlight in red and nothing highlights. Do I need an ABS type of function?

 

[Fluff]

Do I need an ABS type of function?

I've no idea, see post#8