I have an error log coming through which i want to seperate each of the
errors into there own cell on the same row. and at the same time i want each
of the columns to only have the same error codes. example below of 2 rows of
errors

the errors are sperated by ; which i can then use text to columns but this
just seperates them out but does not sort them into be the same columns

example

row 1 orange; apples; pineapples; carrotts, lettuce
row 2 apples; carrotts; potatoes


to then be sorted into the following cells

row 1 orange apples pineapples carrotts lettuce
row 2 apples carrotts potatoes


sorry about the exmple and thanks for the help
pete

If the source data is in field order, then see if you can force it to insert
a ";" in a blank field. Doing so should pad the fields to the same number of
values per row.

What application generates the error log?

P.
--
Overcome Sales Barriers
http://salesbarriers.typepad.com/


"Little pete" wrote:

I have an error log coming through which i want to seperate each of the
errors into there own cell on the same row. and at the same time i want each
of the columns to only have the same error codes. example below of 2 rows of
errors

the errors are sperated by ; which i can then use text to columns but this
just seperates them out but does not sort them into be the same columns

example

row 1 orange; apples; pineapples; carrotts, lettuce
row 2 apples; carrotts; potatoes


to then be sorted into the following cells

row 1 orange apples pineapples carrotts lettuce
row 2 apples carrotts potatoes


sorry about the exmple and thanks for the help
pete

This may work for you.

I put the data in A2:A9999
I put this formula in B2:B9999
=";"&SUBSTITUTE(A2," ","")&";"
This removed the spaces and added semicolons around the whole string:
;orange;apples;pineapples;carrotts,lettuce;


Then I put the words I wanted in C1:F1
And put this formula in C2:
=IF(COUNTIF($B2,"*;"&C$1&";*")0,C$1,"")
and dragged across to F2 and down the rows.




Little pete wrote:

I have an error log coming through which i want to seperate each of the
errors into there own cell on the same row. and at the same time i want each
of the columns to only have the same error codes. example below of 2 rows of
errors

the errors are sperated by ; which i can then use text to columns but this
just seperates them out but does not sort them into be the same columns

example

row 1 orange; apples; pineapples; carrotts, lettuce
row 2 apples; carrotts; potatoes

to then be sorted into the following cells

row 1 orange apples pineapples carrotts lettuce
row 2 apples carrotts potatoes

sorry about the exmple and thanks for the help
pete

--

Dave Peterson