how to separate data in cell into separate cells , sorted by value

hoping i can trouble someone on this forum for some help. i have spreadsheet which has been exported from an online registration system. currently, one of the cells contains a number of various values, separated by commas. i need to have each value appear in a separate cell - but all sorted according to the value. the values however are not all consistent . it's described more clearly in the attached document, but for example.
line 1 . column A: values a,b,d
line 2. column A: values a,c
line 3 column A: value d

how can i make each line have the same values appear in the same column
line 1. [column A: a] [column B : b] [column C: blank] [column D: d]
line 2. [column A: a] [column B : blank] [column C: c] [column D: blank]
....

thanks for your time !

Hello my friend

I inserted one row in row 12 (above the "Racer 1"), then inserted event 1 in C12, event 2 in D12...

To the cels bellow where you want the get the value, I put this formula:

Please Login or Register  to view this content.

Workbook with the sample: sample registration data.xls

Solves your problem?

hi gatti, thank you so much for taking the time to get back to me - and for solving my problem. i've tried to apply the formula you suggested to the actual spreadsheet - but i can't seem to figure out how to adapt the formula you created. for simplicity i had just provide a summary of the problem in my original post. the actual xls report is attached. hate to ask you for more help, but could you look at the attached . the spreadsheet is in the format that it will be generated by the online entry system. the cell that i will need separate is column W. the final version will have about 4000 rows. thanks again !

Hello again

Just checked your file

You have the data in Column W, but where you want to put the values? You gonna split it in other columns? Where the splited "races" will be?

hi gatti, could i just insert 8 blank columns after W as the destination for splited races ?

Oh, ok... Then just insert 8 columns after W (X to AE), then put headers on it (Race 1 to Race 8), and insert the formula (iniciating in X3):

Please Login or Register  to view this content.

The solved workbook:registration sample 1.xlsx

Is this OK?

If it solves your problem, mark the Thread as [SOLVED] and, if possible, add reputatioon