Sort data ...

[n3o]

Hi all the excelforum members,

I'm got a data which neat to be sorted .. wonder if anyone can help me out ... much appreciated .. thanks

what i have

8;
8;
9;
5; 9;
9;
2; 5; 8;

what i need to do

1 2 3 4 5 6 7 8 9
0 0 0 0 0 0 0 1 0
0 0 0 0 0 0 0 1 0
..
.
.
.

my data is in columns one .. i need to put it in 9 columns.. if there is value then the column would be 1 otherwise 0

eg: if 5; 9; then column 5 and 9 the value would be 1 rest would be 0
1 2 3 4 5 6 7 8 9
0 0 0 0 1 0 0 0 1

is there any formula or filter i can use ?

thanks

update: ok .. i just figure out a dumb way.. seperate the file first and then use =IF(OR(A2=2,B2=2,C2=2),1,0) wonder if there is any better one

[im.zeeshan]

Run the following macro:

(I'm assuming your data is in column A and there are no blank rows)

Please Login or Register  to view this content.

[n3o]

woah ... work like miracles .. thanks alot .. you just save my day

[im.zeeshan]

woah ... work like miracles .. thanks alot .. you just save my day

I'm glad I could help.

---------------------------------------------------------------------------------------------------------------
CLICK the STAR icon on the left side below their user name to say thank you to those who had helped you. Thanks.
If you are satisfied with the solution provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.