Issue with Duplicate when sorting a column based another column

[Little_Tiger]

Does anyone could help me with the following:
I have the following table
A B C D E F

Value	ToSort	Normal	Duplicate number ROW()		Correct
5	1	2			2
4	2	2			4
9	3	2			5
4	4	1	5	4	1
4	5	3	6	5	3
114	6	12			12
15	7	7			7
167	8	9			9
18	9	6			6
1564	10	13			13
1654	11	8			8
14	12	10			10
158	13	11			11
16941	14	14			14
		/=IFERROR(VLOOKUP(SMALL(IF(B$2:B$15;A$2:A$15);ROWS($2:2));A$2:B$15;2;FALSE);"")
			/=IF(COUNTIFS($A$1:$A2; A2)>1;ROW();"")

<colgroup><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>

With the formula in C, it is possible to sort column B based on the value in column A. However, as you can see, the first 3 rows in Column C are all "2". (Column F is the correct way Column C should be sorted). This is wrong, as Column F shows. In Column D, I can find the duplicates, and in which respective Cell the duplicates are.

How can I combine the 2 formula, so that if there a duplicate that is move the next one to return the correct value from column B. In principle, I'm trying to get a formula to returns column F.

Thanks
-=LT=-

 

[Peter_SSs]

Can we still use a helper column (D below)? It could be hidden after entering the formulas.
Each formula copied down.

Excel Workbook
	A	B	C	D
1	Value	ToSort	Sorted	Helper
2	5	1	2	4.000002
3	4	2	4	1.000003
4	9	3	5	5.000004
5	4	4	1	1.000005
6	4	5	3	1.000006
7	114	6	12	9.000007
8	15	7	7	7.000008
9	167	8	9	11.000009
10	18	9	6	8.00001
11	1564	10	13	12.000011
12	1654	11	8	13.000012
13	14	12	10	6.000013
14	158	13	11	10.000014
15	16941	14	14	14.000015
Sorted

 

[Peter_SSs]

If you did want to do it without a helper column, try this, It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C
1	Value	ToSort	Sorted
2	5	1	2
3	4	2	4
4	9	3	5
5	4	4	1
6	4	5	3
7	114	6	12
8	15	7	7
9	167	8	9
10	18	9	6
11	1564	10	13
12	1654	11	8
13	14	12	10
14	158	13	11
15	16941	14	14
Sorted (2)

 

[Little_Tiger]

If you did want to do it without a helper column, try this, It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Sorted (2)

	A	B	C
1	Value	ToSort	Sorted
2	5	1	2
3	4	2	4
4	9	3	5
5	4	4	1
6	4	5	3
7	114	6	12
8	15	7	7
9	167	8	9
10	18	9	6
11	1564	10	13
12	1654	11	8
13	14	12	10
14	158	13	11
15	16941	14	14

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 {=INDEX(B$2:B$15,MATCH(SMALL(RANK(A$2:A$15,A$2:A$15,1)+ROW(A$2:A$15)/10^6,ROWS(C$2:C2)),RANK(A$2:A$15,A$2:A$15,1)+ROW(A$2:A$15)/10^6,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter_SSs,

Thank you, for your help. It finally solved my "bugs". Mixing my formula with yours I finally have what I wanted to achieve.
So thank you again. (I couldn't come with rank() Rank.EQ() Excel 2013).

-=LT=-

 

[Peter_SSs]

Peter_SSs,

Thank you, for your help. It finally solved my "bugs". Mixing my formula with yours I finally have what I wanted to achieve.
So thank you again.

Glad you have it sorted.