CODEWITHSUNDEEP
sortingexcel-formula
Kushant Vyas
Kushant Vyas

Reputation: 33

sorting in excel error when duplicate data

I am trying formula for sorting of a column in excel. Data is entered in one column and sorted data is output in another one. Please see attached excel file.

enter image description here

As you can see due to same data in second column I am getting rank 2 twice and VLOOKUP cannot find entry named 3 so it is giving error. When all data entered are unique there is no problem but in case of duplicated data I am having problem. What can I do?

Kbv.

Upvotes: 1

Views: 69

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Just noticed a third possibility that you miight be looking for. So you want to sort the B items where column A indicates the rank of each item in B:

C2
=VLOOKUP(SMALL($A$2:$A$7,ROW(1:1)), $A$2:$B$7, 2,FALSE)

enter image description here

Old answer:

If you want a custom sort where column A indicates the rank of the resulting item inC , copy the following formula in C2 and fill down.

C2
=SMALL(B$2:B$7,A2)

If you want just to copy the column as sorted "naturally", you dont need any helper column, just type this in the first row and fill down (I used column D in my example image below):

D2:
=SMALL(B$2:B$7,ROW(1:1))

enter image description here

Upvotes: 2

Related Questions