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?