Please, help me to list item from A column to D

Hi,

So I will restate the problem how I interpret it, and then suggest a solution that solves it.

Problem: You have various cells populated in column A, each containing a list of items separated by semi-colons. You require in column D a new list, either in traditional spreadsheet format (one item per row) or separated by semi-colons, which is an amalgamated list with duplicates removed.

Solution: Step by step

1. In cell B1 (or wherever else you may find appropriate), insert the formula:
   Formula:

   Please Login or Register  to view this content.

2. Copy B1 and Paste Values into B2
3. With cell B2 selected, go to Data --> Data Tools --> Text to Columns
4. Going through the wizard pages ([N] = Next): select Delimited[N]; then Other and type a semi-colon ensuring the rest are unticked[N]; then make the destination $F$1; and click Finish
5. Select F1:?1 (wherever the list ends), copy it and Paste Transpose into D1, then delete F1:?1
6. Select D1:D?? (wherever the list ends) and go to Data --> Data Tools --> Remove Duplicates and click OK
7. If you need it in a semi-colon-separated format, find a cell and recreate the formula in step 1 for every cell in the new list

I hope that solution makes sense, but I'm happy to give further clarification if it doesn't. If you require a solution that works purely on formulae, let me know and I will give it a go, but be warned, it will be complex because removing duplicates with formulae is particularly awkward.

---
Bernieburnham