Hi Roger,

Perfect! This wins - I noticed you'd spotted a pattern - all the "animals"
(or not in the demo spreadsheet!) must end in TN so I just added TN anything
that didn't end in TN, ran your script (which worked perfectly) and put the
names back to normal.

Absolutely brilliant, and thanks to everyone. I learnt a lot!

"Roger Govier" wrote:

Hi

If you can accept a VBA solution, then this will do what you want

Sub CreateList()

Dim lr As Long, i As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row

k = 0: j = 2
For i = 1 To lr
If Right(ws1.Cells(i, 1), 2) = "TN" Then
k = k + 1: j = 2
ws2.Cells(k, 1) = ws1.Cells(i, 1)

Else
ws2.Cells(k, j) = ws1.Cells(i, 1)
j = j + 1
End If
Next i

End Sub

This will create your List on Sheet2 of the file

To Install
Copy code above
Alt+F11 to invoke the VB Editor
Alt+I+M to insert a New Module
Paste code into White pane that appears
Alt+F11 to return to Excel

To Use
Alt+F8
Select Macro name - CreateList
Run


--
Regards
Roger Govier

jonski wrote:
"Pete_UK" wrote:

You will end up with
something like this:

COW TRUE 1 COW
SHEEP TRUE 2 SHEEP Jim Bob Stu
Jim FALSE RHINO Dave Ollie
Bob FALSE
Stu FALSE
RHINO TRUE 3
Dave FALSE
Ollie FALSE

Then you can fix the values in D1 across and down and then concatenate
your child cells. You can delete the first 3 columns.

Looks good, but I'm getting some weirdness: For example (and I hope the
formatting comes out OK!)

SHEEP TRUE 1 SHEEP Dave Brian Paul
Dave FALSE COW Sue Helen
Brian FALSE CHICKENDave
Paul FALSE HORSE
COW TRUE 2 LIMUR Rik Bob
Sue FALSE
Helen FALSE
CHICKENTRUE 3
Dave FALSE
Roger FALSE
George FALSE
HORSE TRUE 4
Raj FALSE
Pritpal FALSE
Sanjay FALSE
LIMUR TRUE 5
Rik FALSE
Bob FALSE

So we're doing great until CHICKEN, where Roger and George get forgotten
about, then Raj, Pritpal and Sanjay (I'm going for diversity here!)
completely miss out on the HORSE, and we're back to normal for Rik and Bob's
LIMUR experience.

Also, in the bigger full version, there are lots of lines like (for example)

SHEEP TRUE 1 SHEEP Dave Brian Paul COW Rik Bob

Am I allowed to paste links to the file? I know it seems a bit cheeky but if
it helps...
http://stashbox.org/v/836846/example...olumn_file.xls

I really do appreciate the help so far.

I couldn't get Ashish Mathur's other option to work, perhaps it's because I
have Excel 2010 beta? But also, with over 2,000 rows, it appears I have to be
doing something manually. Unless I understood it wrong, that still means the
same amount of work?
.