How to sort this?

[alexpanjhc]

Hi

I have a sorting issue. Here it is

I have currently 7 unique numbers as following (I actually have 90 unique numbers)

ID	Number
1	20121
2	20121,20814,32105
3	43076,20126,55566
4	20814

all the unique numbers are :20121,20126,20814,32106,43076,55566

for each row, I want it the first position is always 20121, the second position is always 20126, and so on. If the row does not have this number, I will use 0 to fill it. so it will look like this.

ID	Number
1	20121,0,0,0,0,0
2	20121,0,20814,32105,0
3	0,20126,0,0,43076,55566
4	0,0,20814,0,0,0

I'd be highly appreciated if anyone please shed some lights,

I am not sure if this can be done easily in excel?

[Anil_Babu_Samineni]

How you are doing in excel?

[#3]

Could you pls share ur application once.

[Frank_Hartmann]

see attached sample!

hope this helps

[ahaahaaha · Accepted solution]

Hi Alex,

Maybe so, as variant (attached files).

Result

The number of unique values in the script is determined automatically. All comments in the script code.

Regards,

Andrey

[alexpanjhc]

Hi Andrey

This is very very helpful.

Can you explain what is your first step doing using while?

I know itno() will increase by 1 each time you execute it. How did the substringcount(number,',') effect in here?

This is a great solution and I just need to understand how it worked.

Thanks!