Conditinal Sorting

[torkunc]

Hi everyone,

In colomn A, I have Data like This:

[TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TİP-1[/TD]
[/TR]
[TR]
[TD]TİP-2[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]TİP-10[/TD]
[/TR]
</tbody>[/TABLE]

As you see some are listed twice and some are three times...

I want to sort them in Colomn B with increment but if data repeats in colomn A, the increments should repeat itself in colomn B. Like this:
A B
------ ------
TİP-1 TİP-1
TİP-2 TİP-2
TİP-5 TİP-3
TİP-5 TİP-3
TİP-5 TİP-3
TİP-9 TİP-4
TİP-9 Tip-4
Tip-10 Tip-5

How can make it by formula?

 

[torkunc]

can anyone please help, it is sth urgent...

 

[torkunc]

Come on, still waiting the answer...

Thnx in advance.

 

[shuklaankur281190]

come on, still waiting the answer...

thnx in advance.

try this one with ctl+shift+enter

=index($a$1:$a$13, match(small(countif($a$1:$a$13, "<"&$a$1:$a$13), row(1:1)), countif($a$1:$a$13, "<"&$a$1:$a$13), 0))

 

[torkunc]

Thanks shuklaankur281190, however it does not give the correct answers when I drag & down:


[TABLE="width: 263"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Answer[/TD]
[TD]Your Formula[/TD]
[/TR]
[TR]
[TD]TİP-1[/TD]
[TD]TİP-1[/TD]
[TD]TİP-1[/TD]
[/TR]
[TR]
[TD]TİP-2[/TD]
[TD]TİP-2[/TD]
[TD]Tip-10[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-2[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]TİP-4[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]Tip-4[/TD]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]Tip-10[/TD]
[TD]Tip-5[/TD]
[TD]TİP-9[/TD]
[/TR]
</tbody>[/TABLE]

 

[shuklaankur281190]

Try this on
="TIP"&"-"&SMALL(VALUE(RIGHT($A$2:$A$15,LEN($A$2:$A$15)-FIND("-",$A$2:$A$15))),ROW(1:1))

 

[shuklaankur281190]

[TABLE="width: 1085"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]List[/TD]
[TD]{="TİP"&"-"&SMALL(VALUE(RIGHT($A$2:$A$16,LEN($A$2:$A$16)-FIND("-",$A$2:$A$16))),ROW(1:1))}[/TD]
[/TR]
[TR]
[TD]TİP-1[/TD]
[TD]TİP-1[/TD]
[/TR]
[TR]
[TD]TİP-2[/TD]
[TD]TİP-1[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-2[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-10[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-3[/TD]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]TİP-15[/TD]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-10[/TD]
[/TR]
[TR]
[TD]TİP-1[/TD]
[TD]TİP-11[/TD]
[/TR]
[TR]
[TD]TİP-11[/TD]
[TD]TİP-15[/TD]
[/TR]
[TR]
[TD]TİP-15[/TD]
[TD]TİP-15[/TD]
[/TR]
</tbody>[/TABLE]

Now check , i think it is working good

 

[torkunc]

My dear friend, I appreciate your effort but still it does not make what I am trying to.


[TABLE="width: 301"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Answer[/TD]
[TD]Your Reply[/TD]
[/TR]
[TR]
[TD]TİP-1[/TD]
[TD]TİP-1[/TD]
[TD]TİP-1[/TD]
[/TR]
[TR]
[TD]TİP-2[/TD]
[TD]TİP-2[/TD]
[TD]TİP-2[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-5[/TD]
[TD]TİP-3[/TD]
[TD]TİP-5[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]TİP-4[/TD]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]TİP-9[/TD]
[TD]TİP-4[/TD]
[TD]TİP-9[/TD]
[/TR]
[TR]
[TD]Tip-10[/TD]
[TD]TİP-5[/TD]
[TD]TİP-10[/TD]
[/TR]
</tbody>[/TABLE]


Maybe it is because of my English; I'll try to express again.

On A Colomn the data has some dublicates. I am trying to put an increment from A to Z. If you examine Answer Colomn above, you see that

1) it takes Type 1 and Type 2 as it is (since they dont have dublicates).
2) when it comes Type 5, as it has 3 times dublicates, it puts the next increment (this time 3) to the all Type-5 (so they turn into Type-3)
3) same also for Type 9. Since there are 2 x Type 9, it puts the next increment (now it is 4) to the both Type 9s (so they turn into Type-4)
5) Next row, it takes Type-10 and convert it to Type-5 (as increment is now 5)

Thanks for your effort and time...

 

[shuklaankur281190]

how much row you have???? or you can send your file once to me on my mail id shukla.ankur281190@gmail.com...

 

[torkunc]

how much row you have????

I have from A1 to A2584...

But in this example A1 to A8...