sorting comma separated data

[jamiedaka]

Please can anyone help an amateur?
I have a list of comma separated postcodes all in a cell.
I have a separate list of postcodes and want this list not to include duplicates from the first list.

I just cant work out how to do it.

the first list contains all the postcodes within 8 miles from a point
the second list contains all the postcodes within 12 miles from the same point.

i need to see the second list only contain postcodes between 8 and 12 miles - hopefully this makes sense?
thanks for any advice.

 

[jasonb75]

I have a list of comma separated postcodes all in a cell.

That is one of the first mistakes that many people make. I would suggest rethinking your sheet layout before attempting to do anything with the data.

Two lists that have one postcode in each cell is easy to compare.
One list that is comma delimited in a single cell and another list that has individual postcodes in separate cells is not so easy but can be done.
Two comma delimited lists is a difficult task and can be error prone.

Another common mistake is merging cells. Merged cells are fine as long as you don't need to look at them with a formula, that's when things can start to get messy.

 

[JLGWhiz]

Provide the following information.

1. Cell reference for cell containing comma separated data. e.g. "A1"
2. Range containing list of individual codes, e.G. "B1:B50"
3. If data is on separate sheets, specifiy sheet names and which contains which set of data.

We cannot see your worksheets, so you must provide information that allows us to know where each type data is located, refer to the worksheets and workbooks involved by name, if more than one of each.

 

[Kerryx]

Odes your version of excel have the option under Data to convert Text to Columns, if yes select the cell containing the csv data convert to split out data into seperate columns, copy all the new columns, paste and paste special , transpose, now you should have a list to compare.

 

[Rick Rothstein]

Provide the following information.

1. Cell reference for cell containing comma separated data. e.g. "A1"
2. Range containing list of individual codes, e.G. "B1:B50"
3. If data is on separate sheets, specifiy sheet names and which contains which set of data.

One more question to add to JLGWhiz's list...

4. Is your data separated by just commas or is there a space after each comma?

 

[jamiedaka]

Thanks all - sorted now with your help.
Couldn't see the wood for the trees!

 

[Kerryx]

All been there.